VersioningProc

-- =======================================================================
-- 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