Multiple Record Update Fire A Trigger Once

Question

Hi everybody,

I have a table where on a single record update, a trigger adds a history record in another table.

When I run a multiple record update on the table the trigger does not work since it was implemented to work with only one record and a trigger runs only once for a statement regardless if it affects multiple records. Is there a way to make this work?

Can I use the following check:

**if @@rowcount = 1**

in order to handle a single record in the way it is handled now and in the else statement to handle it in another way(which I do not know)?

The problem is how can I handle each case atomically when I have multiple records and how can I take every each record from the INSERTED table?

The trigger currently initializes some variables from the INSERTED table, updates the datetime column on the same table and then attempts to insert a history record using the initialized variables I mentioned earlier. So these are the steps I would like to have supporting multiple records.

Can anybody provide me with a simple example or guidelines?

Thank you in advance

Answer 1

A very basic example, without seeing your current, exact trigger logic:

-- First update the datetime column for each record inserted
UPDATE t
SET t.DateField = GETDATE()
FROM UnderlyingTable t
    JOIN inserted i ON t.RecordId = t.RecordId

-- then insert history record for each record inserted
INSERT HistoryTable (DateCreated, RecordId, SomeOtherField)
SELECT GETDATE(), i.RecordId, i.SomeOtherField
FROM inserted i

Answer 2

I found the solution how to make it take into consideration multiple rows.

First I change the date on each Table1 record from the INSERTED table where the PKs match

UPDATE Table1 set status_changed_on = (SELECT CASE WHEN Table1.status is null THEN null ELSE @now END) FROM INSERTED i WHERE Table1.table1_id = i.table1_id

then add a history record like this:

INSERT INTO [History] ([table1_id ], [status_changed_on]) SELECT table1_id, @now FROM INSERTED

and that's it. For each updated record taken from the INSERTED table I insert a history record where @now is the same for all records. It's just a timestamp variable holding the datetime.

The DML above is not the actual since I cannot post my company's code here but you get the picture.

Thank you!!!