June 5, 2013 at 2:06 pm
I have to write a trigger that stores old values in an audit table, I have managed to write the trigger and it updates the table with the old value, I am happy so far, except I have to populate the table with the difference in the update and not the old value (ie) sum of the salaries after the update - sum of salaries before update,
Assuming my update query updates 3 rows, each by 100, I need to include in my trigger code that will subtract the old value (which I have found) from the new value. My problem now is I have no idea how to go about finding the new value! Because I am updating more than one row in the employee table and not saving the new value to the audit table but I need to know it so I can calculate against it.
Here is my current code
CREATE TRIGGER Update_Salaries
ON EMPLOYEE
AFTER UPDATE
AS
BEGIN
IF (COLUMNS_UPDATED() & 14) > 0
BEGIN
INSERT INTO AUDIT
( notes,delta)
VALUES
('Salaries Updated',(SELECT SUM(salary) FROM deleted))
END
END
GO
Any ideas would be greatly appreciated.
June 5, 2013 at 2:29 pm
You're on the right track. Just as you got the old values from the pseudo-table DELETED, you can get the new values from the pseudo-table INSERTED.;-)
June 5, 2013 at 2:32 pm
By the way, are you sure you need "IF (COLUMNS_UPDATED() & 14) > 0" or you just copied from somewhere else?
June 5, 2013 at 3:16 pm
Also keep in mind that your calculation will get the sum of ALL rows in the update. In other words if you update 3 rows, you will get one row in your audit with sum of all the rows that were updated. This seems like it would make the audit somewhat useless because you have no idea what the value was for each row.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2013 at 3:39 pm
Yes, thanks guys! its worked 🙂 I am not sure why, but that is how I have been instructed to calculate it, instead of the individual audit I am to store the difference in total.
I have removed the columns_updated and it still works, please explain to me exactly what the function does, clearly I do not understand it fully from the documentation I have read.
Regards
June 5, 2013 at 5:35 pm
I have to be honest and say that I haven't used it before, but as understood by the documentation, it checks which columns where updated according to their position in the table.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply