document.write('
Data hosted with ♥ by Pastebin.com - Download Raw - See Original
  1. CREATE TRIGGER [StagingArchiver]
  2. ON DATABASE
  3. FOR DDL_TABLE_EVENTS
  4. AS
  5.     DECLARE @EventType  varchar(100) = EVENTDATA().value(\'(/EVENT_INSTANCE/EventType)[1]\',\'nvarchar(max)\');
  6.     DECLARE @SchemaName varchar(100) = EVENTDATA().value(\'(/EVENT_INSTANCE/SchemaName)[1]\',\'nvarchar(max)\');
  7.     DECLARE @ObjectName varchar(100) = dbo.ToSysName( EVENTDATA().value(\'(/EVENT_INSTANCE/ObjectName)[1]\',\'nvarchar(max)\') );
  8.  
  9.     IF @SchemaName IN (\'Archive\', \'Staging\')
  10.     BEGIN
  11.         IF @EventType = \'DROP_TABLE\'            -- If an archvie table is being dropped, then
  12.         BEGIN                                   -- ensure that any corresponding staging table
  13.             IF @SchemaName = \'Archive\'          -- triggers are also dropped.
  14.             BEGIN                              
  15.                 IF OBJECT_ID(\'Staging.ti_\' + @ObjectName) IS NOT NULL
  16.                 BEGIN
  17.                     DECLARE @DropInsertTrigger nvarchar(max) = \'DROP TRIGGER Staging.ti_\' + @ObjectName;
  18.                     EXEC (@DropInsertTrigger)          
  19.                 END
  20.                 IF OBJECT_ID(\'Staging.td_\' + @ObjectName) IS NOT NULL
  21.                 BEGIN
  22.                     DECLARE @DropDeleteTrigger nvarchar(max) = \'DROP TRIGGER Staging.td_\' + @ObjectName;
  23.                     EXEC (@DropDeleteTrigger)          
  24.                 END
  25.             END    
  26.         END
  27.         ELSE IF OBJECT_ID(\'Staging.\' + @ObjectName) IS NOT NULL AND OBJECT_ID(\'Archive.\' + @ObjectName) IS NOT NULL
  28.         BEGIN
  29.             IF @EventType IN (\'CREATE_TABLE\', \'ALTER_TABLE\')
  30.             BEGIN
  31.                 EXEC Staging.CreateStagingTrigger @table_name = @ObjectName;
  32.             END
  33.         END
  34.     END
  35.  
  36. GO
');