SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[tr_d_AUDIT_ErrorLog]
ON [dbo].[ErrorLog]
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: [dbo].[ErrorLog]
-- 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 = '[dbo].[ErrorLog]'
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(
'ErrorLog',
'dbo',
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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorLogID',
CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorLogID] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorTime',
CONVERT(nvarchar(4000), OLD.[ErrorTime], 121),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorTime] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'UserName',
CONVERT(nvarchar(4000), OLD.[UserName], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[UserName] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorNumber',
CONVERT(nvarchar(4000), OLD.[ErrorNumber], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorNumber] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorSeverity',
CONVERT(nvarchar(4000), OLD.[ErrorSeverity], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorSeverity] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorState',
CONVERT(nvarchar(4000), OLD.[ErrorState], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorState] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorProcedure',
CONVERT(nvarchar(4000), OLD.[ErrorProcedure], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorProcedure] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorLine',
CONVERT(nvarchar(4000), OLD.[ErrorLine], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorLine] 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('[ErrorLogID]='+CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0), '[ErrorLogID] Is Null')),
'ErrorMessage',
CONVERT(nvarchar(4000), OLD.[ErrorMessage], 0),
'A'
, CONVERT(nvarchar(500), CONVERT(nvarchar(4000), OLD.[ErrorLogID], 0))
FROM deleted OLD
WHERE
OLD.[ErrorMessage] 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= '[dbo].[tr_d_AUDIT_ErrorLog]', @order='Last', @stmttype='DELETE'
GO
|