SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 need help with trigger, after update, and inserted
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SQLIsTheDevil
Posting Yak Master

USA
177 Posts

Posted - 11/28/2010 :  20:28:05  Show Profile  Reply with Quote
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

USA
37316 Posts

Posted - 11/28/2010 :  20:37:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
177 Posts

Posted - 11/28/2010 :  20:57:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 11/28/2010 :  22:47:24  Show Profile  Reply with Quote
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

USA
177 Posts

Posted - 11/29/2010 :  00:18:38  Show Profile  Reply with Quote
Thank you pointing me in the right direction.
Go to Top of Page

pk_bohra
Flowing Fount of Yak Knowledge

India
1182 Posts

Posted - 11/29/2010 :  03:44:30  Show Profile  Reply with Quote
quote:
Originally posted by SQLIsTheDevil

Thank you pointing me in the right direction.



You are welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000