触发以处理多个行的插入和更新 [英] Trigger to handle multiple row inserts and updates

问题描述

我正在使用以下触发器来跟踪多个表上的插入和更新，并将其记录在日志表中.

I am using the following trigger to track inserts and updates on multiple tables and log it in a log table.

CREATE TRIGGER tr_TestTable1]
ON [TestTable_1]
AFTER INSERT, UPDATE
AS
DECLARE @keyid int, @tn nvarchar(50), @recEditMode nvarchar(50), @trstat nvarchar(50)
BEGIN
    SET NOCOUNT ON;   
    SET @tn = 'TestTable_1'
    IF EXISTS(SELECT 1 FROM INSERTED)
    BEGIN
    SET @recEditMode = (Select REC_EDIT_MODE FROM inserted)
    SET @trstat = 'PENDING'
    SET @keyid = (Select prkeyId FROM inserted)

    IF (@recEditMode = 'MANUAL')
    BEGIN
    IF NOT EXISTS (SELECT * FROM [logTable_1] WHERE SourceKeyId = @keyid AND TrStatus = 'PENDING' AND SourceTableName = @tn)
        BEGIN
            INSERT INTO [logTable_1](SourceKeyId,SourceTableName,TrStatus)
                VALUES (@keyid, @tn, @trstat)       
        END
    END
        END

END

这在单行插入和单行更新时效果很好.我无法优化此代码来处理多行插入和更新.在处理此问题上寻求帮助.

This works fine on single row insert and single row update. I am unable to optimize this code to handle multi row inserts and updates. Looking for some help in handling this.

谢谢.

推荐答案

我按如下所示修改了触发器，现在看来工作得很好...

I modified the trigger as below and it seems to be working fine now...

 CREATE TRIGGER tr_TestTable1]
    ON [TestTable_1]
    AFTER INSERT, UPDATE
    AS
    DECLARE @keyid int, @tn nvarchar(50), @trstat nvarchar(50)
    BEGIN
        IF @@ROWCOUNT = 0 
        RETURN
        SET NOCOUNT ON;   
        IF EXISTS(SELECT * FROM INSERTED)
        BEGIN
        SET @tn = 'TestTable_1'
        SET @trstat = 'PENDING'
        BEGIN
        INSERT INTO LogTable_1 (SourceKeyId, SourceTableName, TrStatus)
        SELECT I.prKeyId, @tn, @trStat FROM INSERTED AS I
        WHERE (I.REC_EDIT_MODE = 'MANUAL' AND NOT EXISTS(SELECT * FROM LogTable_1 WHERE SourceKeyId =   I.prKeyId AND SourceTableName = @tn AND TrStatus = 'PENDING'))

        END
    END
END

这篇关于触发以处理多个行的插入和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！