Trigger to insert into a new table from a deleted row.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Trigger to insert into a new table from a...

Trigger to insert into a new table from a deleted row.

Rate Topic

Display Mode

Topic Options

Author

Message

Pablo Campanini-336244

Posted Monday, February 04, 2013 2:47 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 6:16 AM
Points: 21, Visits: 67

Hello
1.- What would be the code to insert some columns from a deleted record into a different table.

2.- Can I update a field on a table when an INSERT occurs, and when an UPDATE occurs, all in the same trigger?

Any book references or website? Will be much appreciated.

Help Please.

Post #1415502

Lowell

Posted Monday, February 04, 2013 2:51 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

search for "Audit Triggers" on this site...there's a lot of articles and examples:

here's the first article i found, and i reviewed it to confirm it has a decent example:

http://www.sqlservercentral.com/articles/Triggers/auditingtriggers/579/

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1415504

jeetsingh.cs

Posted Tuesday, February 05, 2013 12:16 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:06 PM
Points: 179, Visits: 380

I Think you should learn about the temporary tables created for
#inserted
#deleted so as to maximize their utilization for your task.
these are part of auditing triggers.

Post #1415595

demonfox

Posted Tuesday, February 05, 2013 1:29 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:46 PM
Points: 1,074, Visits: 1,076

Pablo Campanini-336244 (2/4/2013)

-- for 1
The output clause should do the trick

Delete from table A 
output deleted.col1,deleted.col2 into trigger_table(col1,col2)
where id=@id

For 2 , I think it's possible to create trigger for both insert and update ..
Check out this link, it shoudl give the fair idea to create trigger ..

http://msdn.microsoft.com/en-us/library/ms189799(v=sql.110).aspx

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one Ermm

Post #1415637

Pablo Campanini-336244

Posted Tuesday, February 05, 2013 6:20 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 6:16 AM
Points: 21, Visits: 67

Thank you for your help, it is always good to know that there are people that always will help you.... like this Forum... Smile

Post #1415793

« Prev Topic | Next Topic »

Permissions