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.
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 |
|
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 1Name BonusJacob 0John 1000Mary 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 1Name BonusJacob 3000John 3000Mary 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 ChangeInBonus1 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. |
|
|
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 Table1Select 'Jacob', 0 UnionSelect 'John', 1000 UnionSelect 'Mary', 500goCreate Trigger Tr_Up_Table1 On Table1After Update AsBeginDeclare @OldSum IntDeclare @NewSum Int Select @NewSum = sum(Bonus) from Inserted Select @OldSum = sum(Bonus) from Deleted Insert into Table2 (ChangeInBonus) Select @NewSum - isnull(@OldSum,0)EndGoUpdate TAble1 set bonus = 3000Select * from Table2Regards,Bohra |
|
|
SQLIsTheDevil
Posting Yak Master
177 Posts |
Posted - 2010-11-29 : 00:18:38
|
Thank you pointing me in the right direction. |
|
|
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 |
|
|
|
|
|
|
|