StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POTrigger with Multiple Rows
text
Body
copied!<p>I have written a trigger to log changes to a table and I of course didn't realize until after that this only works on one record at a time. Now I am trying to update it to allow for bulk updates and I cannot figure out how to do this.</p> <pre><code>CREATE TRIGGER [DT].[trg_LogChanges] ON [DT].[NewDetails] FOR UPDATE AS DECLARE @TableName VARCHAR(100) , @UpdatedDate smalldatetime , @UpdatedBy uniqueidentifier SELECT @TableName = 'DT.NewDetails' IF EXISTS (SELECT 1 FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid) IF(SELECT ModifiedDate FROM INSERTED) Is Null SET @UpdatedDate = getdate() ELSE SET @UpdatedDate = (SELECT ModifiedDate FROM INSERTED) IF(SELECT ModifiedBy FROM INSERTED) Is Null SET @UpdatedBy = '11111111-1111-1111-1111-111111111111' ELSE SET @UpdatedBy = (SELECT ModifiedBy FROM INSERTED) IF UPDATE (StatusID) BEGIN INSERT INTO DT.LogChanges ( ChangeType, TableName, RecordGuid, FieldName , OldValue, NewValue, UpdatedBy, UpdatedDate ) SELECT 'U', @TableName, d.Guid, 'StatusID' , d.StatusID, i.StatusID, @UpdatedBy, @UpdatedDate FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid WHERE (d.StatusID IS NULL AND i.StatusID IS NOT NULL) OR (d.StatusID IS NOT NULL AND i.StatusID IS NULL) OR (d.StatusID <> i.StatusID) END </code></pre> <p>Can anyone offer up any help on how to fix this to work with multiple rows? I attempted the following by adding the <code>SELECT 1 FROM INSERTED</code> but I still get a subquery error message.</p> <pre><code>CREATE TRIGGER [DT].[trg_LogChanges] ON [DT].[NewDetails] FOR UPDATE AS DECLARE @TableName VARCHAR(100) , @UpdatedDate smalldatetime , @UpdatedBy uniqueidentifier SELECT @TableName = 'DT.NewDetails' IF EXISTS (SELECT 1 FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid) IF(SELECT ModifiedDate FROM INSERTED) Is Null SET @UpdatedDate = getdate() ELSE SET @UpdatedDate = (SELECT ModifiedDate FROM INSERTED) IF(SELECT ModifiedBy FROM INSERTED) Is Null SET @UpdatedBy = '11111111-1111-1111-1111-111111111111' ELSE SET @UpdatedBy = (SELECT ModifiedBy FROM INSERTED) IF UPDATE (StatusID) BEGIN IF EXISTS (SELECT 1 FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid WHERE (d.StatusID IS NULL AND i.StatusID IS NOT NULL) OR (d.StatusID IS NOT NULL AND i.StatusID IS NULL) OR (d.StatusID <> i.StatusID)) BEGIN INSERT INTO DT.LogChanges ( ChangeType, TableName, RecordGuid, FieldName , OldValue, NewValue, UpdatedBy, UpdatedDate ) SELECT 'U', @TableName, d.Guid, 'StatusID' , d.StatusID, i.StatusID, @UpdatedBy, @UpdatedDate FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid WHERE (d.StatusID IS NULL AND i.StatusID IS NOT NULL) OR (d.StatusID IS NOT NULL AND i.StatusID IS NULL) OR (d.StatusID <> i.StatusID) END END </code></pre> <p>I have searched online but obviously I am still missing the correct way to do this. Any help would be greatly appreciated.</p> <p><strong>EDIT</strong> I spoke with the business owners of this process and they want to ignore the updates on multiple rows. Is there a way to add an IF around the entire trigger to ignore it if the records are more than 0?</p> <p>Thanks</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload