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 |
olibara
Yak Posting Veteran
94 Posts |
Posted - 2012-11-14 : 04:40:08
|
Hello I need to create a trigger on Insert or UpdateThe goal is to create a log inserting a new recor in another table using the data of the updated or inserted recordCan I do this in one trigger ?If yes how can I get the data from either updated or inserted record ?Or do I have to create two disstinct trigger for update and insert ?Thank for your help |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-14 : 06:43:30
|
You can handle both update and insert in one trigger.Within the trigger, you have access to two virtual tables - INSERTED and DELETED. INSERTED would contain all the new rows inserted (or rows as they are after update). DELETED would contain all the rows deleted (or rows as they were before the update).So your trigger could conceivably be like this:CREATE TRIGGER dbo.SomeTriggerName ON dbo.YourTableNameFOR UPDATE, INSERT ASINSERT INTO YourAuditTableSELECT *, GETDATE() AS datestampFROM INSERTED;GO There are some really good examples on the MSDN page: http://msdn.microsoft.com/en-us/library/ms189799.aspx |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2012-11-14 : 06:48:06
|
There is no "updated" table...only "deleted" and "inserted". What happens is that when you do an update to a table, the old values (before the update) are put in the deleted table and the new values (after the update) are put in the inserted table. Both are only available inside the scope of the trigger. Whether you should create one or two triggers really depends on the logic between the two operations...- LumbagoMy blog-> http://thefirstsql.com |
|
|
olibara
Yak Posting Veteran
94 Posts |
Posted - 2012-11-14 : 09:41:17
|
Thank a lot sunitabeck and lumbago this is more clear now ! |
|
|
|
|
|