If you are inserting/updating multiple rows in a database and you need to do some processing on the inserted data you may need to add a bit of recursion to your trigger code to handle each record corretly.
The example below uses a while loop to run through each inserted row and updates some relational data in the updated table.
As an addendum… In general, avoid triggers unless you absolutely have to use them
[highlighter]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
CREATE Trigger [dbo].[event_response_contact_trigger] ON [dbo].[_Event_response_contact] AFTER INSERT, UPDATE AS BEGIN CREATE TABLE #inserts ( RowID int IDENTITY(1, 1), id uniqueidentifier, event_response_id uniqueidentifier ) DECLARE @NumberRecords int, @RowCount int DECLARE @ID uniqueidentifier, @ER_ID uniqueidentifier, @E_ID uniqueidentifier INSERT INTO #inserts (id, event_response_id) select id, event_response_id from inserted -- Get the number of records in the temporary table SET @NumberRecords = @@ROWCOUNT SET @RowCount = 1 WHILE @RowCount <= @NumberRecords BEGIN set @E_ID=null SELECT @ID = id, @ER_ID=event_response_id FROM #inserts WHERE RowID = @RowCount select @E_ID = event_id from _event_response where id=@ER_ID update _event_response_contact set event_id=@e_id where id=@ID SET @RowCount = @RowCount + 1 END -- drop the temporary table DROP TABLE #inserts END |
The newid() pelborm IS a real pelborm for o/r mappers: there’s no way to obtain the new value. The main thing is the default value for a field which is newid()’. There’s no way to detect that at runtime. With identity fields, one could simply obtain the latest value and that’s inserted.though in 2005 they introduced NEWSEQUENTIALID(), which IS retrievable with a piece of sql. llblgen pro supports this so auto-guid generation is supported through NEWSEQUENTIALID(). What’s odd is that they didn’t support this in EF. Oh well