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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger on INSERT, UPDATE using INSERTED or Update

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 Update
The goal is to create a log inserting a new recor in another table using the data of the updated or inserted record
Can 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.YourTableName
FOR UPDATE, INSERT
AS

INSERT INTO YourAuditTable
SELECT *, GETDATE() AS datestamp
FROM INSERTED;

GO
There are some really good examples on the MSDN page: http://msdn.microsoft.com/en-us/library/ms189799.aspx
Go to Top of Page

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...

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

olibara
Yak Posting Veteran

94 Posts

Posted - 2012-11-14 : 09:41:17
Thank a lot sunitabeck and lumbago this is more clear now !
Go to Top of Page
   

- Advertisement -