none
Update Triggers for Multiple rows - Linking Deleted rows to Inserted rows... RRS feed

  • Question

  • Alright, this is one that has plagued my mind for a while, given the possibilities presented by triggers, and i'm trying to think of the best way to ask it.  So, I will give an example of an update statement, which may or may not seem utter superfluous, frivolous, and a waste of the TRIGGER functionality, but it is the best way to explain what I'm talking about, so I can correlate it to my actual database for use.  I would post my actual database script, but it's too long, too complicated, and frankly unnecessary for the purposes of my question.

    CREATE TABLE FlagsTable (
       Flag int NOT NULL PRIMARY KEY,
       Description nvarchar(100) NOT NULL
    )

    CREATE TABLE Table1 (
      ID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
      Name nvarchar(100) NULL,
      Flags int NULL,
    )

    INSERT INTO FlagsTable (Flag, Description)
       SELECT 1, "Bit 0"
       UNION
       SELECT 2, "Bit 1"
       UNION
       SELECT 4, "Bit 2"
       UNION
       SELECT 8, "Bit 3"

    UPDATE Table1
    SET Flags=Flags | A.Value
    FROM AnotherTable A inner join Table1 T on (T.ID = A.FK)
    WHERE (Flags & 2 = 2) AND (Flags & 1 = 0)

    Let us Say the values in Table1 Are:
    (1, 'Item 1', 3)
    (2, 'Item 2', 2)
    (3, 'Item 3', 8)
    (4, 'Item 4', 11)
    (5, 'Item 5', 6)
    (6, 'Item 6', 7)

    If I were to write an INSTEAD OF UPDATE Trigger for Table1 we would have multiple rows being updated, so in the TRIGGER body, I would have 2 rows in the inserted and deleted tables respectively (ID's 2,5 from the Table values).  the values i've selected from the AnotherTable used in my psuedo update statement, are say: (9, 1)

    Now, my example is not perfect , persay, but given the above, if I were to write an UPDATE trigger for Table1, how would I correlate that the first row in deleted is supposed to be updated to the first row in inserted ?  Well as I said my example isn't perfect, for given the above example, it is quite simple:

    UPDATE Table1
    SET Flags=Flags | i.flags
    from inserted i inner join table1 t ON (t.ID = i.ID)
    WHERE Exists(select 1 from deleted d where t.id = d.id)

    But let us assume for example that we cannot use the ID Column of Table1 for any comparisons.  LIke, perhaps the UPDATE is UPdating the primary key column of the table.

    SET IDENTITY_INSERT Table1 ON;  --Just so people don't try to contest I can't insert into an Identity field.
    Update Table1
    SET ID=NewID
    FROM NewIDTbl N inner join Table1 T ON (N.OldId = T.ID)
    SET IDENTITY_INSERT Table1 OFF;  --I know one is not supposed to but just let it be for my example at the moment

    WHat will the inserted and deleted table contain then?  I would have a list of rows in the deleted table that have ID values correlating to the ID values contained by Table1, say for the above  the NewIDTbl has the Old & New values respectively (2,7),(4,9),(3,8).  How would I correlate the deleted to inserted tables so that the trigger would react as it should. 

    I can do:

    UPDATE Table1
    SET ID=inserted.ID
    FROM inserted
    WHERE EXISTS (
       SELECT 1
       FROM deleted
       WHERE deleted.ID = Table1.ID
    )

    Ahh, but in the above statement what is there that links the singular row in the deleted table to the appropriate row in the inserted table?  In the Direct SQL Statement, the NewID and OldID values of NewIDTbl are on the same row, so the selection is automatic, but once i get into the TRIGGER, the inserted and deleted tables are representations of the TABLE1 row, not the NewIDTbl row, so I have effectively:

    inserted
    7,'Item 2', 2
    9,'Item 4',11
    8,'Item 3',8

    deleted
    2,'Item 2', 2
    4,'Item 4',11
    3,'Item 3',8

    Since we cannot guarantee in any table that the remaining column values will inherently be unique what can I go on to make sure that the physical row 1 in inserted is equated to physical row 1 of deleted whilst inside the TRIGGER BODY?

    Thanks
    Jaeden "Sifo Dyas" al'Raec Ruiner


    "Never Trust a computer. Your brain is smarter than any micro-chip." PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Monday, December 7, 2009 9:21 PM

Answers

  • The main thing to remember here is that tables do not have an order, regardles of it being a psuedo table or a user table.  Now with that said, how do you link a row from the inserted table with a row in the deleted table?  The answer is quite simple you use the key value that uniquly identifies the row.  I believe the question on the table is how do you keep track of inserted and deleted values, if you update the key value.  This answer is not very well. Sure there are workarounds and ways to do this but you will be making it much harder for yourself and the database logic. I really dont like the concept of updating keys, as this is like playing russian roulette.  The best advice I can give is to try not to update the keys.  If you need this for an actual project, the best answer is to have natural key and perhaps a surrogate key that does not change, this way you always have a key to link back to the origional data.
    http://jahaines.blogspot.com/
    • Marked as answer by JaedenRuiner Tuesday, December 8, 2009 2:53 PM
    Monday, December 7, 2009 11:19 PM
    Moderator

All replies

  • SET IDENTITY_INSERT Table1 ON;  --Just so people don't try to contest I can't insert into an Identity field.
    Update Table1
    SET ID=NewID
    FROM NewIDTbl N inner join Table1 T ON (N.OldId = T.ID)
    SET IDENTITY_INSERT Table1 OFF;  --I know one is not supposed to but just let it be for my example at the moment

    HOw, what, when, where, and why to use the "IDENTITY" column was not my question.
    Respectfully, please answer the question that was asked, not some veiled attempt to nit-pick the flaws in my hastily written examples.  the theory of what i'm asking is all that matters.  this question is about triggers and the inserted and deleted tables, not identity columns.
    J"SD"a'RR
    "Never Trust a computer. Your brain is smarter than any micro-chip." PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Monday, December 7, 2009 9:49 PM
  • The main thing to remember here is that tables do not have an order, regardles of it being a psuedo table or a user table.  Now with that said, how do you link a row from the inserted table with a row in the deleted table?  The answer is quite simple you use the key value that uniquly identifies the row.  I believe the question on the table is how do you keep track of inserted and deleted values, if you update the key value.  This answer is not very well. Sure there are workarounds and ways to do this but you will be making it much harder for yourself and the database logic. I really dont like the concept of updating keys, as this is like playing russian roulette.  The best advice I can give is to try not to update the keys.  If you need this for an actual project, the best answer is to have natural key and perhaps a surrogate key that does not change, this way you always have a key to link back to the origional data.
    http://jahaines.blogspot.com/
    • Marked as answer by JaedenRuiner Tuesday, December 8, 2009 2:53 PM
    Monday, December 7, 2009 11:19 PM
    Moderator
  • The main thing to remember here is that tables do not have an order, regardles of it being a psuedo table or a user table.  Now with that said, how do you link a row from the inserted table with a row in the deleted table?  The answer is quite simple you use the key value that uniquly identifies the row.  I believe the question on the table is how do you keep track of inserted and deleted values, if you update the key value.  This answer is not very well. Sure there are workarounds and ways to do this but you will be making it much harder for yourself and the database logic. I really dont like the concept of updating keys, as this is like playing russian roulette.  The best advice I can give is to try not to update the keys.  If you need this for an actual project, the best answer is to have natural key and perhaps a surrogate key that does not change, this way you always have a key to link back to the origional data.
    http://jahaines.blogspot.com/
    Thank you Adam for answering my actual question. 

    I guess then you would recommend maintaining a dual-key index for each table, like using a identity based integer as well as my 6-character nvarchar, while keeping both as unique indexes, thus the "identity" integer would never be altered, and the user could update the "part number" or what not, that is still a unique index, but never interfering with the true PK. 

    I like that suggestion, very clean. 
    Thanks
    J"SD"a'RR

    "Never Trust a computer. Your brain is smarter than any micro-chip."
    If someone wants your opinion, they'll ask - just answer the question, do not provide commentary or unsolicited critiquing.
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Tuesday, December 8, 2009 2:53 PM