Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

Posting Yak Master

177 Posts

Posted - 11/28/2010 :  20:28:05  Show Profile  Reply with Quote

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.

Almighty SQL Goddess

38200 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

Subscribe to my blog
Go to Top of Page

Posting Yak Master

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

Flowing Fount of Yak Knowledge

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

Create Trigger Tr_Up_Table1 On Table1
After Update
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)


Update TAble1 set bonus = 3000

Select * from Table2

Go to Top of Page

Posting Yak Master

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

Flowing Fount of Yak Knowledge

1182 Posts

Posted - 11/29/2010 :  03:44:30  Show Profile  Reply with 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  
 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.06 seconds. Powered By: Snitz Forums 2000