Audit Logging: Triggers

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

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

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

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

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

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