I have a table that tracks inventory data by each individual piece. This is a simplified version of the table (some non-key fields are excluded): UniqueID, ProductSKU, SerialNumber, OnHandStatus, Cost, DateTimeStamp Every time something happens to a given piece, a new audit record is created. For example, the first time my product ...
I'm using a trigger to store changes in an audit table, I only want to store the values from columns that have been changed. BEGIN IF NEW.history_of_repair_trigger_fired = 1 THEN INSERT INTO history_of_repair SET edit_date_time=NEW.last_edited_date_time, edited_by=NEW.edited_by, repair_id=NEW.repair_id, tenant_name=NEW.tenant_name, prop...
I'm writing some logging/auditing code that will be running in production (not just when errors are thrown or while developing). After reading Coding Horror's experiences with dead-locking and logging, I decided I should seek advice. (Jeff's solution of "not logging" won't work for me, this is legally mandated security auditing) Is the...
Guys, The topic of how to audit tables has recently sprung up in our discussions... so I like your opinion on whats the best way to approach this. We have a mix of both the approaches (which is not good) in our database, as each previous DBA did what he/she believed was the right way. So we need to change them to follow any one model. ...
How can I audit a web application specifically database changes, when the application is connecting to the database as a system account? I would like to use a simple database trigger to write to an audit table but I am unsure of how I would be able to add the user that is logged on instead of the account that is actually doing the work...
we are creating separate audit tables for those entity tables which need auditing using triggers on PostgreSQL 8.4. <Family> <Contact> <Family_Contact> <Address> We have some join tables. Eg : Family_Contact is a join table. Family has 1 or more than 1 contacts. Each contact has an Address. So, we have the following Table structure....
In a previous application version we were using a particular field for a primary key, but because the field may represent different identities across various systems we have made it a non significant field(ie not a primary key or part of a composite primary) however since we dont have another system yet users still use that field as a pr...