Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DClayton77
Starting Member
11 Posts |
 Posted - 2005-03-22 : 13:53:41
|
Hi,I have created a trigger that inserts records into an audit trail table when an update is made on certain fields. The trigger stores the old value, new value, user, datetime etc.I have also coded in logic to check that the field is not being updated to the same value. E.g From 2 to 2. Here is the code for checking just one field. The rest of the trigger is mostly the same code but altered for different field names etc.-- Check that the quantity field has been updated IF UPDATE(CUOD_Quantity_Required) BEGIN -- Check that the field is not being updated to the same value IF EXISTS (SELECT CASE WHEN i.CUOD_Quantity_Required <> d.CUOD_Quantity_Required THEN 1 ELSE 0 END FROM inserted i INNER JOIN deleted d ON i.CUOD_Customer_Order_Detail_ID = d.CUOD_Customer_Order_Detail_ID WHERE CASE WHEN i.CUOD_Quantity_Required <> d.CUOD_Quantity_Required THEN 1 ELSE 0 END = 1) BEGIN --Insert the audit trail record INSERT INTO tblCustomer_Order_Detail_AT ( CUODAT_CUOD_Customer_Order_Detail_ID , CUODAT_Field_Name , CUODAT_Old_Value , CUODAT_New_Value ) SELECT i.CUOD_Customer_Order_Detail_ID, 'Quantity Required', d.CUOD_Quantity_Required, i.CUOD_Quantity_Required FROM inserted i INNER JOIN deleted d ON i.CUOD_Customer_Order_Detail_ID = d.CUOD_Customer_Order_Detail_ID END END If i use query analyzer to manually update one field from 2 to 3, the trigger works and creates a log in the audit trail. If i do the same but update from 2 to 2, the trigger also works by not creating a log in the audit trail.The problem lies in doing a batch update. For example, i have a temporary table which i populate with the records to be updated. I then have a stored procedure that deletes any defunked records, inserts and new records and lastly updates the rest of the records. The last stage (the update) is using a simple UPDATE...FROM...INNER JOIN etc to update the changes between the temporary table and the main table.When the stored procedure runs, i end up with an audit trail for every change made. Even if the field changed from 2 to 2.I appreciate that i could restrict the update to only update where the fields are different but that doesn't solve the mystery of the trigger not working.Any ideas?------------------------------------------------------------------You know, if there's one thing I've learned from being in the army, it's never ignore a pooh-pooh. I knew a major: got pooh-poohed; made the mistake of ignoring the pooh-pooh -- he pooh-poohed it. Fatal error, because it turned out all along that the soldier who pooh-poohed him had been pooh-poohing a lot of other officers, who pooh-poohed their pooh-poohs. In the end, we had to disband the regiment -- morale totally destroyed ... by pooh-pooh! |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-03-22 : 14:09:43
|
| Sure, your insert is for all rows in the set...you are not using the logic you wrote for your check as part of the predicate...Because 1 row in the set is true...you do the insertYou need to add the logic to the insert...and probably could get rid of the existance check.BUT, you are making thing wasy too complicated....All I do is move the row in deleted to history, along with the time it was moved.Then I create a view out of the join between current and history to show the changes...Very simple...Brett8-) |
 |
|
|
DClayton77
Starting Member
11 Posts |
Posted - 2005-03-22 : 14:23:20
|
| Thanks for the reply. I still have a couple of questions:1. How would i add the logic to the insert. Can you give me an example?2. How would your solution eliminate records being entered when a field is updated from 2 to 2. Does the deleted table contain these changes?You know, if there's one thing I've learned from being in the army, it's never ignore a pooh-pooh. I knew a major: got pooh-poohed; made the mistake of ignoring the pooh-pooh -- he pooh-poohed it. Fatal error, because it turned out all along that the soldier who pooh-poohed him had been pooh-poohing a lot of other officers, who pooh-poohed their pooh-poohs. In the end, we had to disband the regiment -- morale totally destroyed ... by pooh-pooh! |
|
|
|
DClayton77
Starting Member
11 Posts |
Posted - 2005-03-22 : 15:49:52
|
| Just re read the question and see what you mean. Sorry, i needed a coffee. Thanks for your helpYou know, if there's one thing I've learned from being in the army, it's never ignore a pooh-pooh. I knew a major: got pooh-poohed; made the mistake of ignoring the pooh-pooh -- he pooh-poohed it. Fatal error, because it turned out all along that the soldier who pooh-poohed him had been pooh-poohing a lot of other officers, who pooh-poohed their pooh-poohs. In the end, we had to disband the regiment -- morale totally destroyed ... by pooh-pooh! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-03-22 : 16:12:47
|
Is this an A.A. Milne story?  |
|
|
|
|
|
|
|
|
Triggers: Doesn't respond to batch update