Audit Logging: Triggers

1. T_ORDER
For Insert: tr_order_i

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tr_order_i')
    BEGIN
        DROP  Trigger tr_order_i
    END
GO

CREATE Trigger tr_order_i ON dbo.T_ORDER 
AFTER INSERT
AS
IF UPDATE(VERSION_NO)
BEGIN
        INSERT [dbo].[T_AUDIT_LOG_DETAIL]
                ([TRANSACTION_NO]
                ,[TABLE_NAME]
                ,[OPERATION_TYPE]
                ,[DATA_CHANGE])
                
        SELECT INSERTED.TRANSACTION_NO
                , 'T_ORDER'
                ,'Insert'
                ,'<dataChange> <after order_id ="'+CONVERT(VARCHAR,INSERTED.ORDER_ID)+'"' +
                ' order_date="' +CONVERT(VARCHAR,INSERTED.ORDER_DATE) + '"' +
                ' supplier="'+INSERTED.SUPPLIER +'"/></dataChange>'
        FROM  INSERTED       
        WHERE NEED_AUDIT  = 1
        
END  

GO

For Update: tr_order_u

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tr_order_u')
    BEGIN
        DROP  Trigger tr_order_u
    END
GO

CREATE Trigger tr_order_u ON dbo.T_ORDER 
AFTER UPDATE
AS
IF UPDATE(VERSION_NO)
BEGIN
        INSERT [dbo].[T_AUDIT_LOG_DETAIL]
                ([TRANSACTION_NO]
                ,[TABLE_NAME]
                ,[OPERATION_TYPE]
                ,[DATA_CHANGE])
        SELECT INSERTED.TRANSACTION_NO
                , 'T_ORDER'
                ,'Update'
                ,'<dataChange> <before order_id ="'+CONVERT(VARCHAR,DELETED.ORDER_ID)+'"' +
                ' order_date="' +CONVERT(VARCHAR,DELETED.ORDER_DATE) + '"' +
                ' supplier="'+DELETED.SUPPLIER +'"/>' +
                '<after order_id ="'+CONVERT(VARCHAR,INSERTED.ORDER_ID)+'"' +
                ' order_date="' +CONVERT(VARCHAR,INSERTED.ORDER_DATE) + '"' +
                ' supplier="'+INSERTED.SUPPLIER +'"/></dataChange>'
        FROM DELETED INNER JOIN INSERTED ON
        DELETED.ORDER_ID = INSERTED.ORDER_ID           
        WHERE INSERTED.NEED_AUDIT  = 1    
        
END    

GO

For Delete: tr_order_d

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tr_order_d')
    BEGIN
        DROP  Trigger tr_order_d
    END
GO

CREATE Trigger tr_order_d ON dbo.T_ORDER 
AFTER DELETE
AS

BEGIN        
        
        INSERT [dbo].[T_AUDIT_LOG_DETAIL]
                ([TRANSACTION_NO]
                ,[TABLE_NAME]
                ,[OPERATION_TYPE]
                ,[DATA_CHANGE])
                
        SELECT TRANSACTION_NO
                , 'T_ORDER'
                ,'Delete'
                ,'<dataChange> <before order_id ="'+CONVERT(VARCHAR,DELETED.ORDER_ID)+'"' +
                ' order_date="' +CONVERT(VARCHAR,DELETED.ORDER_DATE) + '"' +
                ' supplier="'+DELETED.SUPPLIER +'"/></dataChange>' 
        FROM DELETED
END
GO

2. T_ORDER_DETAIL
For Insert: tr_order_detail_i

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'sp_order_detail_i')
    BEGIN
        DROP  Procedure  sp_order_detail_i
    END

GO

CREATE Procedure sp_order_detail_i
    (
        @p_order_id INT,
        @p_product_id INT,
        @p_product_name VARCHAR(50),
        @p_unit_price MONEY,
        @p_quantity INT,
        @p_created_by VARCHAR(50),
        @p_created_on DATETIME,
        @p_last_updated_by VARCHAR(50),
        @p_last_updated_on DATETIME,
        @p_transaction_no CHAR(36)        
    )

AS

INSERT INTO [dbo].[T_ORDER_DETAIL]
           ([ORDER_ID]
           ,[PRODUCT_ID]
           ,[PRODUCT_NAME]
           ,[UNIT_PRICE]
           ,[QUANTITY]
           ,[CREATED_BY]
           ,[CREATED_ON]
           ,[LAST_UPDATED_BY]
           ,[LAST_UPDATED_ON]
           ,[TRANSACTION_NO])
     VALUES
           (@p_order_id,
            @p_product_id,
            @p_product_name,
            @p_unit_price,
            @p_quantity,
            @p_created_by ,
            @p_created_on ,
            @p_last_updated_by ,
            @p_last_updated_on ,
            @p_transaction_no)
            
IF @@ROWCOUNT = 0 
BEGIN
    RAISERROR ('Version conflicts!',10,1)
END
GO

For Update: tr_order_detail_u

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tr_order_detail_u')
    BEGIN
        DROP  Trigger tr_order_detail_u
    END
GO

CREATE Trigger tr_order_detail_u ON dbo.T_ORDER_DETAIL
AFTER UPDATE
AS
IF UPDATE(VERSION_NO)
BEGIN
        INSERT [dbo].[T_AUDIT_LOG_DETAIL]
                ([TRANSACTION_NO]
                ,[TABLE_NAME]
                ,[OPERATION_TYPE]
                ,[DATA_CHANGE])
        SELECT INSERTED.TRANSACTION_NO
                , 'T_ORDER_DETAIL'
                ,'Update'
                ,'<dataChange> <before order_id ="'+CONVERT(VARCHAR,DELETED.ORDER_ID)+'"' +
                ' product_id="' +CONVERT(VARCHAR,DELETED.PRODUCT_ID) + '"' +
                ' product_name="' +CONVERT(VARCHAR,DELETED.PRODUCT_NAME) + '"' +
                ' unit_price="' +CONVERT(VARCHAR,DELETED.UNIT_PRICE) + '"' +
                ' quantity="'+CONVERT(VARCHAR,DELETED.QUANTITY) +'"/>' +
                '<after order_id ="'+CONVERT(VARCHAR,INSERTED.ORDER_ID)+'"' +
                ' product_id="' +CONVERT(VARCHAR,INSERTED.PRODUCT_ID) + '"' +
                ' product_name="' +CONVERT(VARCHAR,INSERTED.PRODUCT_NAME) + '"' +
                ' unit_price="' +CONVERT(VARCHAR,INSERTED.UNIT_PRICE) + '"' +
                ' quantity="'+CONVERT(VARCHAR,INSERTED.QUANTITY) +'"/></dataChange>'
        FROM DELETED INNER JOIN INSERTED ON
        DELETED.ORDER_ID = INSERTED.ORDER_ID          
        AND     DELETED.PRODUCT_ID = INSERTED.PRODUCT_ID      
        WHERE INSERTED.NEED_AUDIT  = 1    

END   

GO

For Delete: tr_order_detail_d

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tr_order_detail_d')
    BEGIN
        DROP  Trigger tr_order_detail_d
    END
GO

CREATE Trigger tr_order_detail_d ON dbo.T_ORDER_DETAIL
AFTER DELETE
AS        

        INSERT [dbo].[T_AUDIT_LOG_DETAIL]
                ([TRANSACTION_NO]
                ,[TABLE_NAME]
                ,[OPERATION_TYPE]
                ,[DATA_CHANGE])
        SELECT DELETED.TRANSACTION_NO
                , 'T_ORDER_DETAIL'
                ,'Delete'
                ,'<dataChange> <before order_id ="'+CONVERT(VARCHAR,DELETED.ORDER_ID)+'"' +
                ' product_id="' +CONVERT(VARCHAR,DELETED.PRODUCT_ID) + '"' +
                ' product_name="' +CONVERT(VARCHAR,DELETED.PRODUCT_NAME) + '"' +
                ' unit_price="' +CONVERT(VARCHAR,DELETED.UNIT_PRICE) + '"' +
                ' quantity="'+CONVERT(VARCHAR,DELETED.QUANTITY) +'"/></dataChange>'
        FROM DELETED       
GO

作者：蒋金楠
微信公众账号：大内老A
微博：www.weibo.com/artech
如果你想及时得到个人撰写文章以及著作的消息推送，或者想看看个人推荐的技术资料，可以扫描左边二维码（或者长按识别二维码）关注个人公众号（原来公众帐号蒋金楠的自媒体将会停用）。
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

原文链接