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 |
|
sparrow37
Posting Yak Master
148 Posts |
 Posted - 2010-04-25 : 14:27:33
|
| Hi all,I have a strored procedure which has an update statement to update all student records. But before or after updating all the records in that table, I want to stored changed/modified rows in another table. How can I do these both things ?Regards,Asif Hameed |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-25 : 14:52:19
|
| you just need to create a trigger on table student for update and inside write insert statement to insert old/new values before after updation to your table by using INSERTED (contains new/modified values) and DELETED (contains old values before updation) tables------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-25 : 15:58:36
|
Or you can write an insert statement in your stored procedure before you make the update.The insert statement should have the same WHERE condition as the update statement.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-25 : 16:01:02
|
But if you write a trigger like visakh suggested, then the data is stored in that other table regardless from where the changes are coming.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-04-25 : 16:03:12
|
| If you are on 2005 or later, lookup the OUTPUT clause for both insert and update statements. The output clause can output the affected rows so you can insert those rows to another table. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-04-25 : 16:05:04
|
quote:
Originally posted by webfred
But if you write a trigger like visakh suggested, then the data is stored in that other table regardless from where the changes are coming.
No, you're never too old to Yak'n'Roll if you're too young to die.
And, if you don't allow access to insert/update to the table except through your stored procedures - you have the same result. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-26 : 00:22:30
|
quote:
Originally posted by jeffw8713
quote:
Originally posted by webfred
But if you write a trigger like visakh suggested, then the data is stored in that other table regardless from where the changes are coming.
No, you're never too old to Yak'n'Roll if you're too young to die.
And, if you don't allow access to insert/update to the table except through your stored procedures - you have the same result.
But that may not be possible always as there can be more than one processes/application which might require the population of this table along with some other tables based on related details and may not always call this procedure. So safest bet would be trigger i guess!------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
update/ insert