Post reply

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

  • February 4, 2013 at 2:47 pm

    #270280

    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.

  • February 4, 2013 at 2:51 pm

    #1584124

    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

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 5, 2013 at 12:16 am

    #1584188

    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.

  • February 5, 2013 at 1:29 am

    #1584213

    Pablo Campanini-336244 (2/4/2013)

    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.

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

  • February 5, 2013 at 6:20 am

    #1584346

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply