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
|