Post reply

SQL server Trigger

  • June 8, 2015 at 7:47 pm

    #318059

    Hi,

    I have a delete trigger on one table. When I delete a single row in this table that deleted record will be recorded to a history table.

    But sometimes my delete will delete more than one record. In this case also only one record is being recorded into the history table.

    How can I record all the deleted records into the history table when multiple records are deleted with one single delete statement?

    Thanks.

  • June 8, 2015 at 8:23 pm

    #1803861

    sql_novice_2007 (6/8/2015)

    Hi,

    I have a delete trigger on one table. When I delete a single row in this table that deleted record will be recorded to a history table.

    But sometimes my delete will delete more than one record. In this case also only one record is being recorded into the history table.

    How can I record all the deleted records into the history table when multiple records are deleted with one single delete statement?

    Thanks.

    It is virtually certain that you declare variables and put the DELETEd table record(s) into the variables and then use them. This is a CRITICALLY BAD flaw, and one for which I had a client GO OUT OF BUSINESS (and yes, I even told them 14 months in advance that this would happen!!). It is also VERY common. Triggers process the ENTIRE BATCH of DML at the same time. 0-N rows, where N is the number of affected rows. So your trigger MUST be able to process 0-N rows. Best is a set-based operation. But if you can't figure that out and can't get help to do so, then build a CURSOR to make sure you handle every row. In this case performance must take a back seat to getting the right answer! 🙂

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • June 9, 2015 at 6:11 am

    #1803918

    Yes, I am declaring variables and then put the DELETEd table record(s) into the variables and then use them to record them to the deleted table.

    CREATE TRIGGER [dbo].[DeptItemdelete]

    ON [dbo].[Item]

    AFTER DELETE

    AS

    Declare @DeptID int, @ItemID int;

    SELECT @DeptID = DeptID, @ItemID= ItemID FROM DELETED

    Insert into dbo.ItemsDeleted( DeptID, ItemID ) Values( @DeptID, @ItemID)

    Can I have some sample code to handle every row?

    Thanks.

  • June 9, 2015 at 6:16 am

    #1803919

    Why write it that way? Why not just a

    INSERT INTO ...

    SELECT ... FROM deleted

    ?

    When you write triggers, you must assume that the inserted and deleted tables have multiple rows and write your code to handle any number of rows in them

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 9, 2015 at 6:44 am

    #1803929

    Thanks to both of you.

    It worked perfectly for me.

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

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