Trigger: [AdventureWorks2016CTP3].[HumanResources].[tr_d_AUDIT_Employee] on Table Employee

CollapseAll image

Collapse image Trigger properties


Name  Value 
 Schema   [HumanResources] 
 Owner   [dbo] 
 Creation date   21.09.2016 
 Encrypted   
 Disabled   
 ID   2043870348 
 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_d_AUDIT_Employee] 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] Table 1
Total 4 object(s)

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [HumanResources].[tr_d_AUDIT_Employee]
ON [HumanResources].[Employee]
FOR DELETE

    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:  DELETE TRIGGER for Table:  [HumanResources].[Employee]
-- 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),
        --@TABLE_NAME                nvarchar(4000),
         @ROWS_COUNT                int

    SET NOCOUNT ON

    --Set @TABLE_NAME = '[HumanResources].[Employee]'
    Select @ROWS_COUNT=count(*) from deleted
    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',
        'HumanResources',
        3,    --    ACTION ID For DELETE
        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()
    


    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'BusinessEntityID',
        CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[BusinessEntityID] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'NationalIDNumber',
        CONVERT(nvarchar(4000), OLD.[NationalIDNumber], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[NationalIDNumber] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'LoginID',
        CONVERT(nvarchar(4000), OLD.[LoginID], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[LoginID] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'OrganizationNode',
        CONVERT(nvarchar(max), CONVERT(nvarchar(max), OLD.[OrganizationNode])),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[OrganizationNode] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'JobTitle',
        CONVERT(nvarchar(4000), OLD.[JobTitle], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[JobTitle] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'BirthDate',
        CONVERT(nvarchar(4000), OLD.[BirthDate], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[BirthDate] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'MaritalStatus',
        CONVERT(nvarchar(4000), OLD.[MaritalStatus], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[MaritalStatus] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'Gender',
        CONVERT(nvarchar(4000), OLD.[Gender], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[Gender] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'HireDate',
        CONVERT(nvarchar(4000), OLD.[HireDate], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[HireDate] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'SalariedFlag',
        CONVERT(nvarchar(4000), OLD.[SalariedFlag], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[SalariedFlag] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'VacationHours',
        CONVERT(nvarchar(4000), OLD.[VacationHours], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[VacationHours] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'SickLeaveHours',
        CONVERT(nvarchar(4000), OLD.[SickLeaveHours], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[SickLeaveHours] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'CurrentFlag',
        CONVERT(nvarchar(4000), OLD.[CurrentFlag], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[CurrentFlag] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'rowguid',
        CONVERT(nvarchar(4000), OLD.[rowguid], 0),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[rowguid] Is Not Null

    INSERT
    INTO [AdventureWorks2016CTP3].dbo.AUDIT_LOG_DATA
    (
        AUDIT_LOG_TRANSACTION_ID,
        PRIMARY_KEY_DATA,
        COL_NAME,
        OLD_VALUE_LONG,
        DATA_TYPE
        , KEY1
    )
    SELECT
        @AUDIT_LOG_TRANSACTION_ID,
        convert(nvarchar(1500), IsNull('[BusinessEntityID]='+CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0), '[BusinessEntityID] Is Null')),
        'ModifiedDate',
        CONVERT(nvarchar(4000), OLD.[ModifiedDate], 121),
        'A'
        ,  CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[BusinessEntityID], 0))
    FROM deleted OLD
    WHERE
        OLD.[ModifiedDate] Is Not Null

    -- Lookup
    

  -- 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_d_AUDIT_Employee]', @order='Last', @stmttype='DELETE'
GO

Collapse image See also


List of triggers

[HumanResources].[Employee]