-- =======================================================================
-- Author: Mark White
--
-- Description: Generates an "instead of" trigger on a staging table, to
-- retire old records by moving deleted records into an
-- archive table.
-- =======================================================================
ALTER PROC [Staging].[CreateStagingTrigger]
@table_name varchar(max) = null
AS
BEGIN
DECLARE @sql varchar(max);
DECLARE @table varchar(max);
DECLARE @trigger varchar(max);
DECLARE @d_trigger varchar(max);
DECLARE @d_columns varchar(max) = \'\';
DECLARE @d_join varchar(max) = \'\';
DECLARE @i_trigger varchar(max);
DECLARE @i_columns varchar(max) = \'\';
DECLARE @i_join varchar(max) = \'\';
SET NOCOUNT ON;
DECLARE @object_id INT = OBJECT_ID(\'Staging.\' + @table_name);
DECLARE @archive_id INT = OBJECT_ID(\'Archive.\' + @table_name);
if @object_id IS NULL or @archive_id IS NULL
BEGIN
RAISERROR(\'Triggers can only be to staging tables that have a corresponding archive table.\',10,1);
RETURN;
END
-- get the qualified table and trigger name
SELECT TOP 1
@table = dbo.ToSysname(t.name),
@d_trigger = \'[\' + s.name + \'].[td_\' + t.name + \']\',
@i_trigger = \'[\' + s.name + \'].[ti_\' + t.name + \']\'
FROM sys.tables t
join sys.schemas s on s.schema_id = t.schema_id
and t.object_id = @object_id;
IF object_id(@d_trigger) IS NOT NULL
BEGIN
SET @sql = \'DROP TRIGGER \' + @d_trigger;
EXEC (@sql);
END;
IF object_id(@i_trigger) IS NOT NULL
BEGIN
SET @sql = \'DROP TRIGGER \' + @i_trigger;
EXEC (@sql);
END;
-- Get Archive (deleted) columns and joins
SELECT @d_columns = @d_columns + CASE WHEN c.is_computed = 1 THEN \'\' ELSE dbo.ToSysname(c.name) + \', \' END,
@d_join = @d_join + CASE WHEN i.is_primary_key = 1
THEN \' AND S.\' + dbo.ToSysname(c.name) + \' = T.\' + dbo.ToSysname(c.name)
ELSE \'\' END
FROM sys.columns c
JOIN sys.types t on t.user_type_id = c.user_type_id
LEFT JOIN sys.indexes i
JOIN sys.index_columns ic on ic.index_id = i.index_id and ic.object_id = i.object_id
ON i.object_id = c.object_id and i.is_primary_key = 1 and c.column_id = ic.column_id
where c.object_id = @archive_id
and exists (select * from sys.columns cc where cc.object_id = @object_id and cc.name = c.name)
order by c.column_id;
-- Get Staging (insert) columns and joins
SELECT @i_columns = @i_columns + dbo.ToSysname(c.name) + \', \',
@i_join = @i_join + CASE WHEN i.is_primary_key = 1
THEN \' AND S.\' + dbo.ToSysname(c.name) + \' = T.\' + dbo.ToSysname(c.name)
ELSE \'\' END
FROM sys.columns c
JOIN sys.types t on t.user_type_id = c.user_type_id
LEFT JOIN sys.indexes i
JOIN sys.index_columns ic on ic.index_id = i.index_id and ic.object_id = i.object_id
ON i.object_id = c.object_id and i.is_primary_key = 1 and c.column_id = ic.column_id
where c.object_id = @object_id
and c.name not in (\'RecordEndDate\')
and c.is_computed = 0
order by c.column_id;
IF LEN(@d_join) = 0 OR LEN(@i_join) = 0
BEGIN
--RAISERROR(\'Versioning trigger can only be added to tables that have a primary key set.\',10,1);
RETURN;
END
SET @d_columns = SUBSTRING(@d_columns, 1,LEN(@d_columns) - 1);
SET @d_join = SUBSTRING(@d_join, 6, 80000);
SET @i_columns = SUBSTRING(@i_columns, 1,LEN(@i_columns) - 1);
SET @i_join = SUBSTRING(@i_join, 6, 80000);
SET @sql = \'\'
+\'CREATE TRIGGER \' + @d_trigger + \' ON Staging.\' + @table + CHAR(13)
+\' AFTER DELETE\' + CHAR(13)
+\'AS \' + CHAR(13)
+\'BEGIN \' + CHAR(13)
+\' SET NOCOUNT ON; \' + CHAR(13)
+ CHAR(13)
+\' MERGE INTO Archive.\' + @table + \' AS T\' + CHAR(13)
+\' USING deleted AS S \'+ CHAR(13)
+\' ON \' + @d_join + CHAR(13)
+\' WHEN NOT MATCHED THEN\' + CHAR(13)
+\' INSERT (\' + @d_columns + \')\' + CHAR(13)
+\' VALUES (\' + @d_columns + \');\' + CHAR(13)
+\'END\' + CHAR(13)
EXEC (@sql);
SET @sql =
+\'CREATE TRIGGER \' + @i_trigger + \' ON Staging.\' + @table + CHAR(13)
+\' INSTEAD OF INSERT\' + CHAR(13)
+\'AS \' + CHAR(13)
+\'BEGIN \' + CHAR(13)
+\' SET NOCOUNT ON; \' + CHAR(13)
+ CHAR(13)
+\' MERGE INTO Staging.\' + @table + \' AS T\' + CHAR(13)
+\' USING inserted AS S \'+ CHAR(13)
+\' ON \'
+ REPLACE(REPLACE(@i_join
,\'S.DataStageID = T.DataStageID\', \'S.DataStageID < T.DataStageID\')
,\'S.RecordVersion = T.RecordVersion\', \'S.RecordVersion < T.RecordVersion\') + CHAR(13)
+\' WHEN MATCHED THEN DELETE;\' + CHAR(13)
+ CHAR(13)
--+\' MERGE INTO Staging.\' + @table + \' AS T\' + CHAR(13)
--+\' USING inserted AS S \'+ CHAR(13)
--+\' ON \' + @i_join + CHAR(13)
--+\' WHEN NOT MATCHED THEN\' + CHAR(13)
--+\' INSERT (\' + @i_columns + \')\' + CHAR(13)
--+\' VALUES (\' + @i_columns + \');\' + CHAR(13)
+\' INSERT Staging.\' + @table + CHAR(13)
+\' (\' + @i_columns + \')\' + CHAR(13)
+\' SELECT \' + @i_columns + \' FROM inserted;\' + CHAR(13)
+\'END\' + CHAR(13)
+ CHAR(13)
EXEC (@sql);
END