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.
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
Can anyone offer up any help on how to fix this to work with multiple rows? I attempted the following by adding the SELECT 1 FROM INSERTED
but I still get a subquery error message.
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
I have searched online but obviously I am still missing the correct way to do this. Any help would be greatly appreciated.
EDIT 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?
Thanks
I believe your conditional checks can be minimized to CASE statements. It seems like you could minimize the entire trigger to
INSERT INTO DT.LogChanges (ChangeType, TableName, RecordGuid, FieldName, OldValue, NewValue, UpdatedBy, UpdatedDate)
SELECT 'U', 'DT.NewDetails', d.Guid, 'StatusID', d.StatusID, i.StatusID,
CASE WHEN i.ModifiedBy IS NULL THEN GETDATE() ELSE i.ModifiedBy AS ModifiedBy,
CASE WHEN i.ModifiedDate IS NULL THEN '11111111-1111-1111-1111-111111111111' ELSE i.ModifiedDate AS ModifiedDate
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)
I didn't have time to make sure the syntax is perfect, but if you have some problems with it I can assist.
In response to your EDIT, that's done pretty easily with something like
IF (SELECT COUNT(*) FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid) = 1
BEGIN
PRINT 'Only one update record'
END
精彩评论