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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 need help with trigger, after update, and inserted

Author  Topic 

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-11-28 : 20:28:05
Hello,

The Subject line is not long enough, unfortunately, to properly communicate a theme for my plight. So, thanks for clicking in advance.

I am creating a trigger that monitors another table's update operation on a money column called Bonus. And let's call the table Compensation_Tbl. When the Bonus column is updated, whether it is for one row, two rows, ten rows, all rows, etcetera, the total change in the Bonus column is recorded in a different Table called Compensation_Tbl_Audit as a column called Change_In_Bonus.

i.e. 5 people are given bonuses of 1000,2000,1000,3000, and 500, respectively. So, when the bonuses column is updated, the trigger will insert 7500 in the Change_In_Bonus column of the Compensation_Tbl_Audit table.

The problem is two-fold: One, I am trying to write "select * from "INSERTED" and it is not working. It is giving the error of 'Invalid Object name "INSERTED".' Second, what is the best way to approach this task with the trigger?

I am trying to do a trigger after update and check the accumulative value of Bonus column before and after the update. Then, take the difference and insert it into the Compensation_Tbl_Audit table's ChangeInBonus column. That is what I think might be a good solution. The problem is I'm not exactly positive I know how to do it.

Any help would be most appreciated. Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-28 : 20:37:51
Could you show us some more sample data? I got a bit confused even though I read your post a few times.

Show us sample updates, along with what should be put into the audit table. You at first mentioned 7500 and then switched it to the difference between the before and the after, so I got a bit confused.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-11-28 : 20:57:55
Sorry about that.

I figured out the problem I was having with inserted and deleted. I stupidly forgot to include it in the trigger when I was running a test. So, inesrted and deleted works just fine.

Now, to clarify the issue. I need to run a trigger on a table (table 1) after an update operation. The trigger will insert the change in the Bonus column into another table (table 2).

So, Table 1 has columns Name and Bonus. Something like this:


Table 1
Name Bonus
Jacob 0
John 1000
Mary 500


Say an update operation is issued like this where everyone know has a total of 3000 in the Bonus column:


update [Table 1]
set Bonus=3000


So, after the update, Table 1 will look like this:


Table 1
Name Bonus
Jacob 3000
John 3000
Mary 3000


What I want the trigger to do is take the total difference across all names before the update operation and after the update operation on Table 1 on the Bonus column and insert that difference in the a column in Table 2.

So, Table 2 would like this after the trigger executes:


ID ChangeInBonus
1 7500


Notice it reads 7500 because 3000+3000+3000 - 0-1000-500=7500.

I hope that clarifies what I'm trying to do.

Thank you.
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-28 : 22:47:24
Something to start with:

Create Table Table1
(
Name varchar(255),
Bonus Integer
)

Create table Table2
(Id int identity,
ChangeInBonus Int
)

Insert into Table1
Select 'Jacob', 0 Union
Select 'John', 1000 Union
Select 'Mary', 500
go

Create Trigger Tr_Up_Table1 On Table1
After Update
As
Begin
Declare @OldSum Int
Declare @NewSum Int

Select @NewSum = sum(Bonus) from Inserted
Select @OldSum = sum(Bonus) from Deleted

Insert into Table2 (ChangeInBonus)
Select @NewSum - isnull(@OldSum,0)

End
Go

Update TAble1 set bonus = 3000

Select * from Table2

Regards,
Bohra
Go to Top of Page

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2010-11-29 : 00:18:38
Thank you pointing me in the right direction.
Go to Top of Page

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-29 : 03:44:30
quote:
Originally posted by SQLIsTheDevil

Thank you pointing me in the right direction.



You are welcome
Go to Top of Page
   

- Advertisement -