sql 批量触发器

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

ALTER   TRIGGER [dbo].[tr_insert_update_delete_sscode]
ON [dbo].[SSCODE]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    
    DECLARE @v_tableName VARCHAR(4000) = 'SSCODE';
    DECLARE @v_columns VARCHAR(4000) = 'ID, TYPEID, CODE, NAME, PYDM, PCODE, PARAM, ISACTIVE, ORDERID';
    

    DECLARE @v_id VARCHAR(20)=''
    DECLARE @v_action VARCHAR(1) = ''
    DECLARE @v_isrows INT=0
    DECLARE @v_row_index INT=1

    --增加
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        SET @v_action = '1'
        SELECT @v_id=Inserted.ID FROM inserted
        SELECT @v_isrows=COUNT(1) FROM inserted
    END
    --修改
    ELSE IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        SET @v_action = '2'
        SELECT @v_id=Inserted.ID FROM inserted
        SELECT @v_isrows=COUNT(1) FROM inserted
    END
    --删除
    ELSE IF NOT EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        SET @v_action = '3'
        SELECT @v_id=deleted.ID FROM deleted
        SELECT @v_isrows=COUNT(1) FROM deleted
    END

    --当行修改
    IF @v_isrows=1
    BEGIN
        PRINT '单行逻辑'
        --调用存储过程
        EXEC dbo.PROC_Sync_Start @i_columns = @v_columns,   -- varchar(8000)
                                    @i_tablename = @v_tableName, -- varchar(1000)
                                    @i_id = @v_id,        -- varchar(20)
                                    @i_action = @v_action     -- varchar(1)
    END
    --多行修改
    ELSE IF @v_isrows>1
    BEGIN
        PRINT '多行逻辑'
        --创建临时表结构
        CREATE TABLE #TableRows
        (
            RId INT IDENTITY(1, 1) NOT NULL,
            Id VARCHAR(20),
            PRIMARY KEY (RId)
        );
        --插入操作数据到id集合表。
        IF    @v_action='1' OR @v_action='2'
        BEGIN
            INSERT #TableRows(Id)
            SELECT ID FROM Inserted
        END
        ELSE IF @v_action = '3'
        BEGIN
            INSERT #TableRows(Id)
            SELECT ID FROM Inserted
        END

        DECLARE @v_index_id VARCHAR(20)=''
        WHILE    @v_row_index<=@v_isrows
        BEGIN
            SELECT @v_index_id=Id FROM #TableRows  WHERE RId=@v_row_index
            --调用存储过程
            EXEC dbo.PROC_Sync_Start @i_columns = @v_columns,   -- varchar(8000)
                                     @i_tablename = @v_tableName, -- varchar(1000)
                                     @i_id = @v_index_id,        -- varchar(20)
                                     @i_action = @v_action     -- varchar(1)
            SET @v_row_index=@v_row_index+1
        END
    END
    

END;

GO

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:        WUXIAODONG 
-- Create date: 2019-08-12 13:01:07
-- Description:    <基础数据同步插入队列消息>
-- =============================================
ALTER   PROCEDURE PROC_Sync_Start
(
    @i_columns VARCHAR(8000),
    @i_tablename VARCHAR(1000),
    @i_id VARCHAR(20),
    @i_action VARCHAR(1)
)
AS
BEGIN
    SET NOCOUNT ON;
        --SELECT STUFF((select CODE,NAME,PYDM,PCODE,PARAM,ISACTIVE,ORDERID from SSCODE where id='000000458EA840000CCA' for xml path('DATA')),1,0,'')
        DECLARE @v_sql NVARCHAR(4000)
        DECLARE @v_rtn VARCHAR(max)=''
        IF @i_action<>'3'
        BEGIN
             SET @v_sql='SELECT @v_data=STUFF((select '+@i_columns+' from '+@i_tableName +' where id='''+@i_id+''' for xml path(''DATA'')),1,0,'''')'
            PRINT @v_sql
            EXECUTE sp_executesql @v_sql,N'@v_data VARCHAR(max) output',@v_rtn output 
        END
        
        INSERT syncsmmq ( ID, EXCHANGENAME, ROUTINGKEY, MSGBODY, CDATE, DELAYTIME, PROCESDATE, BASEID, MEMO, STATUS ) 
        VALUES ( dbo.FUNC_SS_NEWID('0000','0000','C',NEWID()), 'sync_basic_data', 'N', '{"ID":"'+@i_id+'","TableCode":"'+@i_tablename+'","columns":"'+@i_columns+'","Data":"'+@v_rtn+'","Action":"'+@i_action+'"}', GETDATE(), 0, GETDATE(), '', '', 1 )
    
END 




GO