DROP PROCEDURE IF EXISTS dev.sp_Global_GenerateAsyncAuditTrigger
GO
CREATE PROCEDURE dev.sp_Global_GenerateAsyncAuditTrigger
@tableName nvarchar(50)
,@disabled bit = 0
AS
DECLARE
@newColumnList nvarchar(max)
,@oldColumnList nvarchar(max)
,@SQL nvarchar(max)
,@ColumnIntezmenyId nvarchar(1000) = '0'
,@ColumnTanevId nvarchar(1000) = '0'
,@JoinKeys nvarchar(4000);
SELECT @newColumnList = ISNULL(@newColumnList + ', ', '') + 'i.' + COLUMN_NAME + N' AS ''new.' + COLUMN_NAME + '''',
@oldColumnList = ISNULL(@oldColumnList + ', ', '') + 'd.' + COLUMN_NAME + N' AS ''old.' + COLUMN_NAME + ''''
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName
AND TABLE_SCHEMA = 'dbo'
AND DATA_TYPE NOT IN ('varbinary', 'binary')
AND COLUMN_NAME NOT IN ('SERIAL', 'LASTCHANGED', 'CREATED', 'ID', 'C_INTEZMENYID', 'C_ALINTEZMENYID', 'C_TANEVID', 'C_ALTANEVID');
SELECT @ColumnIntezmenyId = CASE WHEN TABLE_NAME = 'T_INTEZMENY' THEN 'i.ID' ELSE 'i.' + COLUMN_NAME END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME IN ('C_INTEZMENYID', 'C_ALINTEZMENYID');
SELECT @ColumnTanevId = CASE WHEN TABLE_NAME = 'T_TANEV' THEN 'i.ID' ELSE 'i.' + COLUMN_NAME END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tableName AND TABLE_SCHEMA = 'dbo' AND COLUMN_NAME IN ('C_TANEVID', 'C_ALTANEVID');
SELECT @JoinKeys = COALESCE(@JoinKeys + ' AND ', '') + 'i.' + c.name + ' = d.' + c.name
FROM sys.key_constraints kc
INNER JOIN sys.index_columns ic ON ic.index_id = kc.unique_index_id AND ic.object_id = kc.parent_object_id
INNER JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE kc.parent_object_id = object_id('dbo.' + @tableName)
and kc.type = 'PK'
ORDER BY ic.index_column_id ASC;
SET @SQL = '
CREATE OR ALTER TRIGGER [tr_AsyncAudit' + UPPER(SUBSTRING(@tableName, 3, 100)) + '] ON [' + @tableName + ']
WITH EXECUTE AS ''kreta_tech_user''
FOR INSERT, UPDATE, DELETE
AS
DECLARE @rowcount INT = @@ROWCOUNT;
DECLARE @json NVARCHAR(MAX);
DECLARE @key INT = 0;
DECLARE @limit INT = 1000;
DECLARE @action TINYINT = 0;
DECLARE @now NVARCHAR(20) = CONVERT(NVARCHAR(20), GETDATE(), 120);
DECLARE @handle UNIQUEIDENTIFIER;
DECLARE @userid INT;
SET NOCOUNT ON;
SET @action += CASE WHEN EXISTS(SELECT * FROM inserted) THEN 1 ELSE 0 END;
SET @action += CASE WHEN EXISTS(SELECT * FROM deleted) THEN 2 ELSE 0 END;
IF @rowcount = 0 SET @rowcount = (SELECT COUNT(*) FROM inserted);
IF @rowcount = 0 SET @rowcount = (SELECT COUNT(*) FROM deleted);
IF @rowcount = 0 RETURN;
SET @userid = TRY_CAST(SESSION_CONTEXT(N''USERID'') AS INT);
' + IIF(EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName AND COLUMN_NAME = 'MODIFIER'), 'IF ISNULL(@userid, 0) = 0 AND (UPDATE(MODIFIER) OR UPDATE(CREATOR)) SET @userid = (SELECT TOP 1 NULLIF(ISNULL(MODIFIER, CREATOR), 0) FROM inserted);', '') + '
IF @rowcount > @limit
BEGIN
SELECT TOP 0 * INTO #audit_trigger_inserted FROM inserted UNION ALL SELECT TOP 0 * FROM inserted;
SELECT TOP 0 * INTO #audit_trigger_deleted FROM deleted UNION ALL SELECT TOP 0 * FROM deleted;
ALTER TABLE #audit_trigger_inserted ADD TRIGGER_ADDED_KEY INT NOT NULL IDENTITY(1, 1);
ALTER TABLE #audit_trigger_deleted ADD TRIGGER_ADDED_KEY INT NOT NULL IDENTITY(1, 1);
INSERT INTO #audit_trigger_inserted SELECT * FROM inserted;
INSERT INTO #audit_trigger_deleted SELECT * FROM deleted;
ALTER TABLE #audit_trigger_inserted ADD PRIMARY KEY CLUSTERED(TRIGGER_ADDED_KEY);
ALTER TABLE #audit_trigger_deleted ADD PRIMARY KEY CLUSTERED(TRIGGER_ADDED_KEY);
-- ALTER TABLE #audit_trigger_inserted ADD UNIQUE NONCLUSTERED (TRIGGER_ADDED_KEY);
-- ALTER TABLE #audit_trigger_deleted ADD UNIQUE NONCLUSTERED (TRIGGER_ADDED_KEY);
END
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE AuditService
TO SERVICE ''AuditService''
ON CONTRACT AuditContract
WITH ENCRYPTION = OFF, LIFETIME = 10000;
WHILE (1 = 1)
BEGIN
IF @action = 1 -- INSERT
IF @rowcount > @limit
BEGIN
SET @json = (
SELECT TOP (@limit)
i.ID AS ID,
' + @ColumnTanevId + ' AS TID,
' + @ColumnIntezmenyId + ' AS IID,
ISNULL(i.MODIFIER, i.CREATOR) AS [UID],
' + @newColumnList + '
FROM #audit_trigger_inserted i
WHERE i.TRIGGER_ADDED_KEY > @key
ORDER BY i.TRIGGER_ADDED_KEY ASC
FOR JSON PATH, INCLUDE_NULL_VALUES
)
SELECT TOP (@limit) @key = TRIGGER_ADDED_KEY FROM #audit_trigger_inserted WHERE TRIGGER_ADDED_KEY > @key ORDER BY TRIGGER_ADDED_KEY;
END
ELSE
SET @json = (
SELECT
i.ID AS ID,
' + @ColumnTanevId + ' AS TID,
' + @ColumnIntezmenyId + ' AS IID,
ISNULL(i.MODIFIER, i.CREATOR) AS [UID],
' + @newColumnList + '
FROM inserted i
FOR JSON PATH, INCLUDE_NULL_VALUES
)
IF @action = 2 -- DELETE
IF @rowcount > @limit
BEGIN
SET @json = (
SELECT TOP (@limit)
d.ID AS ID,
' + replace(@ColumnTanevId, 'i.', 'd.') + ' AS TID,
' + replace(@ColumnIntezmenyId, 'i.', 'd.') + ' AS IID,
ISNULL(d.MODIFIER, d.CREATOR) AS [UID],
' + @oldColumnList + '
FROM #audit_trigger_deleted d
WHERE d.TRIGGER_ADDED_KEY > @key
ORDER BY d.TRIGGER_ADDED_KEY ASC
FOR JSON PATH, INCLUDE_NULL_VALUES
)
SELECT TOP (@limit) @key = TRIGGER_ADDED_KEY FROM #audit_trigger_deleted WHERE TRIGGER_ADDED_KEY > @key ORDER BY TRIGGER_ADDED_KEY;
END
ELSE
SET @json = (
SELECT
d.ID AS ID,
' + replace(@ColumnTanevId, 'i.', 'd.') + ' AS TID,
' + replace(@ColumnIntezmenyId, 'i.', 'd.') + ' AS IID,
ISNULL(d.MODIFIER, d.CREATOR) AS [UID],
' + @oldColumnList + '
FROM deleted d
FOR JSON PATH, INCLUDE_NULL_VALUES
)
IF @action = 3 --UPDATE
IF @rowcount > @limit
BEGIN
SET @json = (
SELECT TOP (@limit)
i.ID AS ID,
' + @ColumnTanevId + ' AS TID,
' + @ColumnIntezmenyId + ' AS IID,
ISNULL(i.MODIFIER, i.CREATOR) AS [UID],
' + @oldColumnList + ',
' + @newColumnList + '
FROM #audit_trigger_inserted i
INNER JOIN #audit_trigger_deleted d ON i.TRIGGER_ADDED_KEY = d.TRIGGER_ADDED_KEY
WHERE i.TRIGGER_ADDED_KEY > @key
ORDER BY i.TRIGGER_ADDED_KEY ASC
FOR JSON PATH, INCLUDE_NULL_VALUES
)
SELECT TOP (@limit) @key = TRIGGER_ADDED_KEY FROM #audit_trigger_inserted WHERE TRIGGER_ADDED_KEY > @key ORDER BY TRIGGER_ADDED_KEY;
END
ELSE
SET @json = (
SELECT
i.ID AS ID,
' + @ColumnTanevId + ' AS TID,
' + @ColumnIntezmenyId + ' AS IID,
ISNULL(i.MODIFIER, i.CREATOR) AS [UID],
' + @oldColumnList + ',
' + @newColumnList + '
FROM inserted i
INNER JOIN deleted d ON ' + @JoinKeys + '
FOR JSON PATH, INCLUDE_NULL_VALUES
)
IF @json IS NULL BREAK;
SET @json = CONCAT(
''{'',
''"ENTITY":"' + @tableName + '",'',
''"REASON":"'', CHOOSE(@action, ''New'', ''Removed'', ''Modified''), ''",'',
''"DATETIME":"'', @now, ''",'',
''"ACTION":'', @action, '','',
''"HOST":"'', HOST_NAME(), ''",'',
''"LOGIN":"'', REPLACE(ORIGINAL_LOGIN(), ''\'', ''\\''), ''",'',
''"USERID":'', COALESCE(CAST(@userid AS VARCHAR(10)), ''null''), '','',
''"ROWCOUNT":'', @rowcount, '', '',
''"ROWS": '', @json,
''}'');
SEND ON CONVERSATION @handle MESSAGE TYPE AuditMessage (@json);
IF @rowcount <= @limit BREAK;
END
END CONVERSATION @handle;
'
-- SELECT @SQL
EXEC sys.sp_executesql @SQL;
IF @disabled = 1
BEGIN
SET @SQL = 'DISABLE TRIGGER [tr_AsyncAudit' + UPPER(SUBSTRING(@tableName, 3, 100)) + '] ON [' + @tableName + ']';
EXEC(@SQL);
END
GO