Trigger: [AdventureWorks2016CTP3].[HumanResources].[tr_u_AUDIT_Employee_Temporal] on Table Employee_Temporal

CollapseAll image

Collapse image Trigger properties


Name  Value 
 Schema   [HumanResources] 
 Owner   [dbo] 
 Creation date   21.09.2016 
 Encrypted   
 Disabled   
 ID   2059870405 
 Implementation type   Transact SQL 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Type


Instead of Insert Update Delete
     

Collapse image Objects that [HumanResources].[tr_u_AUDIT_Employee_Temporal] depends on


Object name Object type Dep level
[HumanResources] Schema 1
[dbo].[AUDIT_LOG_DATA] Table 1
[dbo].[AUDIT_LOG_TRANSACTIONS] Table 1
[HumanResources].[Employee_Temporal] Table 1
Total 4 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [HumanResources].[tr_u_AUDIT_Employee_Temporal]
ON [HumanResources].[Employee_Temporal]
FOR UPDATE

    NOT FOR REPLICATION

As
-- "<TAG>SQLAUDIT GENERATED - DO NOT REMOVE</TAG>"
-- --------------------------------------------------------------------------------------------------------------
-- Legal:        You may freely edit and modify this template and make copies of it.
-- Description:  UPDATE TRIGGER for Table:  [HumanResources].[Employee_Temporal]
-- Author:       ApexSQL Software
-- Date:         9/21/2016 1:23:24 AM
-- --------------------------------------------------------------------------------------------------------------
BEGIN
    DECLARE 
        @IDENTITY_SAVE            varchar(50),
        @AUDIT_LOG_TRANSACTION_ID    Int,
        @PRIM_KEY            nvarchar(4000),
        @Inserted                bit,
        --@TABLE_NAME                nvarchar(4000),
         @ROWS_COUNT                int

    SET NOCOUNT ON

    --Set @TABLE_NAME = '[HumanResources].[Employee_Temporal]'
    Select @ROWS_COUNT=count(*) from inserted
    SET @IDENTITY_SAVE = CAST(IsNull(@@IDENTITY,1) AS varchar(50))

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_TRANSACTIONS
    (
        TABLE_NAME,
        TABLE_SCHEMA,
        AUDIT_ACTION_ID,
        HOST_NAME,
        APP_NAME,
        MODIFIED_BY,
        MODIFIED_DATE,
        AFFECTED_ROWS,
        [DATABASE]
    )
    values(
        'Employee_Temporal',
        'HumanResources',
        1,    --    ACTION ID For UPDATE
        CASE 
          WHEN LEN(HOST_NAME()) < 1 THEN ' '
          ELSE HOST_NAME()
        END,
        CASE 
          WHEN LEN(APP_NAME()) < 1 THEN ' '
          ELSE APP_NAME()
        END,
        SUSER_SNAME(),
        GETDATE(),
        @ROWS_COUNT,
        'AdventureWorks2016CTP3'
    )

    
    Set @AUDIT_LOG_TRANSACTION_ID = SCOPE_IDENTITY()
    

    SET @Inserted = 0
    
    If UPDATE([BusinessEntityID])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'BusinessEntityID',
            CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0),
            CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Full Outer Join inserted NEW On
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            WHERE (
            
            
                (
                    NEW.[BusinessEntityID] <>
                    OLD.[BusinessEntityID]
                ) Or
            
                (
                    NEW.[BusinessEntityID] Is Null And
                    OLD.[BusinessEntityID] Is Not Null
                ) Or
                (
                    NEW.[BusinessEntityID] Is Not Null And
                    OLD.[BusinessEntityID] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([NationalIDNumber])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'NationalIDNumber',
            CONVERT(nvarchar(4000), OLD.[NationalIDNumber], 0),
            CONVERT(nvarchar(4000), NEW.[NationalIDNumber], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[NationalIDNumber] <>
                    OLD.[NationalIDNumber]
                ) Or
            
                (
                    NEW.[NationalIDNumber] Is Null And
                    OLD.[NationalIDNumber] Is Not Null
                ) Or
                (
                    NEW.[NationalIDNumber] Is Not Null And
                    OLD.[NationalIDNumber] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([LoginID])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'LoginID',
            CONVERT(nvarchar(4000), OLD.[LoginID], 0),
            CONVERT(nvarchar(4000), NEW.[LoginID], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[LoginID] <>
                    OLD.[LoginID]
                ) Or
            
                (
                    NEW.[LoginID] Is Null And
                    OLD.[LoginID] Is Not Null
                ) Or
                (
                    NEW.[LoginID] Is Not Null And
                    OLD.[LoginID] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([OrganizationNode])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'OrganizationNode',
            CONVERT(nvarchar(max), CONVERT(nvarchar(max), OLD.[OrganizationNode])),
            CONVERT(nvarchar(max), CONVERT(nvarchar(max), NEW.[OrganizationNode])),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    CONVERT(nvarchar(max), NEW.[OrganizationNode]) <>
                    CONVERT(nvarchar(max), OLD.[OrganizationNode])
                ) Or
            
                (
                    NEW.[OrganizationNode] Is Null And
                    OLD.[OrganizationNode] Is Not Null
                ) Or
                (
                    NEW.[OrganizationNode] Is Not Null And
                    OLD.[OrganizationNode] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([JobTitle])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'JobTitle',
            CONVERT(nvarchar(4000), OLD.[JobTitle], 0),
            CONVERT(nvarchar(4000), NEW.[JobTitle], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[JobTitle] <>
                    OLD.[JobTitle]
                ) Or
            
                (
                    NEW.[JobTitle] Is Null And
                    OLD.[JobTitle] Is Not Null
                ) Or
                (
                    NEW.[JobTitle] Is Not Null And
                    OLD.[JobTitle] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([BirthDate])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'BirthDate',
            CONVERT(nvarchar(4000), OLD.[BirthDate], 0),
            CONVERT(nvarchar(4000), NEW.[BirthDate], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[BirthDate] <>
                    OLD.[BirthDate]
                ) Or
            
                (
                    NEW.[BirthDate] Is Null And
                    OLD.[BirthDate] Is Not Null
                ) Or
                (
                    NEW.[BirthDate] Is Not Null And
                    OLD.[BirthDate] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([MaritalStatus])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'MaritalStatus',
            CONVERT(nvarchar(4000), OLD.[MaritalStatus], 0),
            CONVERT(nvarchar(4000), NEW.[MaritalStatus], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[MaritalStatus] <>
                    OLD.[MaritalStatus]
                ) Or
            
                (
                    NEW.[MaritalStatus] Is Null And
                    OLD.[MaritalStatus] Is Not Null
                ) Or
                (
                    NEW.[MaritalStatus] Is Not Null And
                    OLD.[MaritalStatus] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([Gender])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'Gender',
            CONVERT(nvarchar(4000), OLD.[Gender], 0),
            CONVERT(nvarchar(4000), NEW.[Gender], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[Gender] <>
                    OLD.[Gender]
                ) Or
            
                (
                    NEW.[Gender] Is Null And
                    OLD.[Gender] Is Not Null
                ) Or
                (
                    NEW.[Gender] Is Not Null And
                    OLD.[Gender] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([HireDate])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'HireDate',
            CONVERT(nvarchar(4000), OLD.[HireDate], 0),
            CONVERT(nvarchar(4000), NEW.[HireDate], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[HireDate] <>
                    OLD.[HireDate]
                ) Or
            
                (
                    NEW.[HireDate] Is Null And
                    OLD.[HireDate] Is Not Null
                ) Or
                (
                    NEW.[HireDate] Is Not Null And
                    OLD.[HireDate] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([VacationHours])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'VacationHours',
            CONVERT(nvarchar(4000), OLD.[VacationHours], 0),
            CONVERT(nvarchar(4000), NEW.[VacationHours], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[VacationHours] <>
                    OLD.[VacationHours]
                ) Or
            
                (
                    NEW.[VacationHours] Is Null And
                    OLD.[VacationHours] Is Not Null
                ) Or
                (
                    NEW.[VacationHours] Is Not Null And
                    OLD.[VacationHours] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([SickLeaveHours])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'SickLeaveHours',
            CONVERT(nvarchar(4000), OLD.[SickLeaveHours], 0),
            CONVERT(nvarchar(4000), NEW.[SickLeaveHours], 0),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[SickLeaveHours] <>
                    OLD.[SickLeaveHours]
                ) Or
            
                (
                    NEW.[SickLeaveHours] Is Null And
                    OLD.[SickLeaveHours] Is Not Null
                ) Or
                (
                    NEW.[SickLeaveHours] Is Not Null And
                    OLD.[SickLeaveHours] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([ValidFrom])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'ValidFrom',
            CONVERT(nvarchar(4000), OLD.[ValidFrom], 121),
            CONVERT(nvarchar(4000), NEW.[ValidFrom], 121),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[ValidFrom] <>
                    OLD.[ValidFrom]
                ) Or
            
                (
                    NEW.[ValidFrom] Is Null And
                    OLD.[ValidFrom] Is Not Null
                ) Or
                (
                    NEW.[ValidFrom] Is Not Null And
                    OLD.[ValidFrom] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    If UPDATE([ValidTo])
    BEGIN
    
        INSERT
        INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
        (
            AUDIT_LOG_TRANSACTION_ID,
            PRIMARY_KEY_DATA,
            COL_NAME,
            OLD_VALUE_LONG,
            NEW_VALUE_LONG,
            DATA_TYPE
            , KEY1
        )
        SELECT
            @AUDIT_LOG_TRANSACTION_ID,
            convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), IsNull(OLD.[BusinessEntityID], NEW.[BusinessEntityID]), 0), '[BusinessEntityID] Is Null')),
            'ValidTo',
            CONVERT(nvarchar(4000), OLD.[ValidTo], 121),
            CONVERT(nvarchar(4000), NEW.[ValidTo], 121),
            'A'
            , IsNULL( CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0)), CONVERT(nvarchar(500), CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)))
            
        FROM deleted OLD Inner Join inserted NEW On 
            (CONVERT(nvarchar(4000), NEW.[BusinessEntityID], 0)=CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0) or (NEW.[BusinessEntityID] Is Null and OLD.[BusinessEntityID] Is Null))
            where (
            
            
                (
                    NEW.[ValidTo] <>
                    OLD.[ValidTo]
                ) Or
            
                (
                    NEW.[ValidTo] Is Null And
                    OLD.[ValidTo] Is Not Null
                ) Or
                (
                    NEW.[ValidTo] Is Not Null And
                    OLD.[ValidTo] Is Null
                )
                )
        
        SET @Inserted = CASE WHEN @@ROWCOUNT > 0 Then 1 Else @Inserted End
    END
    
    -- Watch
    
    -- Lookup
    
    IF @Inserted = 0
    BEGIN
        DELETE FROM [AdventureWorks2016CTP3].dbo.AUDIT_LOG_TRANSACTIONS WHERE AUDIT_LOG_TRANSACTION_ID = @AUDIT_LOG_TRANSACTION_ID
    END

  -- Restore @@IDENTITY Value
  
    DECLARE @maxprec AS varchar(2)
    SET @maxprec=CAST(@@MAX_PRECISION as varchar(2))
    EXEC('SELECT IDENTITY(decimal('+@maxprec+',0),'+@IDENTITY_SAVE+',1) id INTO #tmp')
  
End
GO
EXEC sp_settriggerorder @triggername= '[HumanResources].[tr_u_AUDIT_Employee_Temporal]', @order='Last', @stmttype='UPDATE'
GO

Collapse image See also


List of triggers

[HumanResources].[Employee_Temporal]