document.write('
Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  1. -- =======================================================================
  2. -- Author:      Mark White
  3. --                                                                       
  4. -- Description: Generates an "instead of" trigger on a staging table, to
  5. --              retire old records by moving deleted records into an
  6. --              archive table.
  7. -- =======================================================================
  8. ALTER PROC [Staging].[CreateStagingTrigger]
  9.     @table_name varchar(max) = null
  10. AS
  11. BEGIN
  12.     DECLARE @sql varchar(max);
  13.     DECLARE @table varchar(max);
  14.     DECLARE @trigger varchar(max);
  15.     DECLARE @d_trigger varchar(max);
  16.     DECLARE @d_columns varchar(max) = \'\';
  17.     DECLARE @d_join varchar(max) = \'\';
  18.     DECLARE @i_trigger varchar(max);
  19.     DECLARE @i_columns varchar(max) = \'\';
  20.     DECLARE @i_join varchar(max) = \'\';
  21.    
  22.     SET NOCOUNT ON;
  23.    
  24.     DECLARE @object_id INT  = OBJECT_ID(\'Staging.\' + @table_name);
  25.     DECLARE @archive_id INT = OBJECT_ID(\'Archive.\' + @table_name);
  26.    
  27.     if @object_id IS NULL or @archive_id IS NULL
  28.     BEGIN
  29.         RAISERROR(\'Triggers can only be to staging tables that have a corresponding archive table.\',10,1);
  30.         RETURN;
  31.     END
  32.    
  33.     -- get the qualified table and trigger name
  34.     SELECT TOP 1
  35.         @table   =  dbo.ToSysname(t.name),
  36.         @d_trigger =  \'[\' + s.name + \'].[td_\' + t.name + \']\',
  37.         @i_trigger =  \'[\' + s.name + \'].[ti_\' + t.name + \']\'
  38.         FROM sys.tables t
  39.         join sys.schemas s on s.schema_id = t.schema_id
  40.                           and t.object_id = @object_id;
  41.  
  42.     IF object_id(@d_trigger) IS NOT NULL
  43.     BEGIN
  44.         SET @sql = \'DROP TRIGGER \' + @d_trigger;
  45.         EXEC (@sql);
  46.     END;
  47.  
  48.     IF object_id(@i_trigger) IS NOT NULL
  49.     BEGIN
  50.         SET @sql = \'DROP TRIGGER \' + @i_trigger;
  51.         EXEC (@sql);
  52.     END;
  53.  
  54.     -- Get Archive (deleted) columns and joins
  55.     SELECT @d_columns = @d_columns + CASE WHEN c.is_computed = 1 THEN \'\' ELSE dbo.ToSysname(c.name) + \', \' END,
  56.            @d_join = @d_join +  CASE WHEN i.is_primary_key = 1
  57.                             THEN \' AND S.\' + dbo.ToSysname(c.name) + \' = T.\' + dbo.ToSysname(c.name)
  58.                             ELSE \'\' END
  59.     FROM sys.columns c
  60.     JOIN sys.types t on t.user_type_id = c.user_type_id
  61.     LEFT JOIN sys.indexes i
  62.          JOIN sys.index_columns ic on ic.index_id = i.index_id and ic.object_id = i.object_id
  63.          ON i.object_id = c.object_id and i.is_primary_key = 1 and c.column_id = ic.column_id
  64.     where c.object_id = @archive_id
  65.     and exists (select * from sys.columns cc where cc.object_id = @object_id and cc.name = c.name)
  66.     order by c.column_id;
  67.  
  68.     -- Get Staging (insert) columns and joins
  69.     SELECT @i_columns = @i_columns + dbo.ToSysname(c.name) + \', \',
  70.            @i_join = @i_join +  CASE WHEN i.is_primary_key = 1
  71.                                 THEN \' AND S.\' + dbo.ToSysname(c.name) + \' = T.\' + dbo.ToSysname(c.name)
  72.                                 ELSE \'\' END
  73.     FROM sys.columns c
  74.     JOIN sys.types t on t.user_type_id = c.user_type_id
  75.     LEFT JOIN sys.indexes i
  76.          JOIN sys.index_columns ic on ic.index_id = i.index_id and ic.object_id = i.object_id
  77.          ON i.object_id = c.object_id and i.is_primary_key = 1 and c.column_id = ic.column_id
  78.     where c.object_id = @object_id
  79.     and c.name not in (\'RecordEndDate\')
  80.     and c.is_computed = 0
  81.     order by c.column_id;
  82.  
  83.  
  84.     IF LEN(@d_join) = 0 OR LEN(@i_join) = 0
  85.     BEGIN
  86.         --RAISERROR(\'Versioning trigger can only be added to tables that have a primary key set.\',10,1);
  87.         RETURN;
  88.     END
  89.  
  90.     SET @d_columns = SUBSTRING(@d_columns, 1,LEN(@d_columns) - 1);
  91.     SET @d_join = SUBSTRING(@d_join, 6, 80000);
  92.     SET @i_columns = SUBSTRING(@i_columns, 1,LEN(@i_columns) - 1);
  93.     SET @i_join = SUBSTRING(@i_join, 6, 80000);
  94.  
  95.     SET @sql = \'\'
  96.     +\'CREATE TRIGGER \' + @d_trigger + \' ON Staging.\' + @table + CHAR(13)
  97.     +\'   AFTER DELETE\' + CHAR(13)
  98.     +\'AS \' + CHAR(13)
  99.     +\'BEGIN \' + CHAR(13)
  100.     +\'  SET NOCOUNT ON; \' + CHAR(13)
  101.     + CHAR(13)
  102.     +\'  MERGE INTO Archive.\' + @table + \' AS T\' + CHAR(13)
  103.     +\'  USING deleted AS S \'+ CHAR(13)
  104.     +\'      ON \' + @d_join + CHAR(13)
  105.     +\'  WHEN NOT MATCHED THEN\' + CHAR(13)
  106.     +\'      INSERT (\' + @d_columns + \')\' + CHAR(13)
  107.     +\'      VALUES (\' + @d_columns + \');\' + CHAR(13)
  108.     +\'END\' + CHAR(13)
  109.  
  110.     EXEC (@sql);
  111.    
  112.     SET @sql =
  113.     +\'CREATE TRIGGER \' + @i_trigger + \' ON Staging.\' + @table + CHAR(13)
  114.     +\'   INSTEAD OF INSERT\' + CHAR(13)
  115.     +\'AS \' + CHAR(13)
  116.     +\'BEGIN \' + CHAR(13)
  117.     +\'  SET NOCOUNT ON; \' + CHAR(13)
  118.     + CHAR(13)
  119.      +\' MERGE INTO Staging.\' + @table + \' AS T\' + CHAR(13)
  120.      +\' USING inserted AS S \'+ CHAR(13)
  121.      +\'     ON \'
  122.      + REPLACE(REPLACE(@i_join
  123.             ,\'S.DataStageID = T.DataStageID\', \'S.DataStageID < T.DataStageID\')
  124.             ,\'S.RecordVersion = T.RecordVersion\', \'S.RecordVersion < T.RecordVersion\')  + CHAR(13)
  125.     +\'  WHEN MATCHED THEN DELETE;\' + CHAR(13)
  126.     + CHAR(13)
  127.     --+\'    MERGE INTO Staging.\' + @table + \' AS T\' + CHAR(13)
  128.     --+\'    USING inserted AS S \'+ CHAR(13)
  129.     --+\'        ON \' + @i_join + CHAR(13)
  130.     --+\'    WHEN NOT MATCHED THEN\' + CHAR(13)
  131.     --+\'        INSERT (\' + @i_columns + \')\' + CHAR(13)
  132.     --+\'        VALUES (\' + @i_columns + \');\' + CHAR(13)
  133.     +\'  INSERT Staging.\' + @table + CHAR(13)
  134.     +\'             (\' + @i_columns + \')\' + CHAR(13)
  135.     +\'  SELECT \' + @i_columns + \' FROM inserted;\' + CHAR(13)
  136.     +\'END\' + CHAR(13)
  137.     + CHAR(13)
  138.  
  139.     EXEC (@sql);
  140. END
');