Trigger with calculations - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger with calculations

Author

Topic

soulchyld21
Starting Member

28 Posts

Posted - 2013-06-05 : 15:19:45

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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-05 : 23:07:49

don't you also want to know whose salary is being updated ?

KH
[spoiler]Time is always against us[/spoiler]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-05 : 23:23:00

on an update trigger The [inserted] table will have the new values the [deleted] table will have the old values. strange audit table - if you update 10 people's salary you'll just have one row?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 00:09:10

The trigger in its current format doesnt make much sense to me. Whats the purpose of finding difference between aggregates of salaries? I guess what you may have to do is to get it at the employee level atleast to capture his increment
As for your problem TGs suggestion is what you need to implement.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources