CREATE TRIGGER [StagingArchiver]
ON DATABASE
FOR DDL_TABLE_EVENTS
AS
DECLARE @EventType varchar(100) = EVENTDATA().value(\'(/EVENT_INSTANCE/EventType)[1]\',\'nvarchar(max)\');
DECLARE @SchemaName varchar(100) = EVENTDATA().value(\'(/EVENT_INSTANCE/SchemaName)[1]\',\'nvarchar(max)\');
DECLARE @ObjectName varchar(100) = dbo.ToSysName( EVENTDATA().value(\'(/EVENT_INSTANCE/ObjectName)[1]\',\'nvarchar(max)\') );
IF @SchemaName IN (\'Archive\', \'Staging\')
BEGIN
IF @EventType = \'DROP_TABLE\' -- If an archvie table is being dropped, then
BEGIN -- ensure that any corresponding staging table
IF @SchemaName = \'Archive\' -- triggers are also dropped.
BEGIN
IF OBJECT_ID(\'Staging.ti_\' + @ObjectName) IS NOT NULL
BEGIN
DECLARE @DropInsertTrigger nvarchar(max) = \'DROP TRIGGER Staging.ti_\' + @ObjectName;
EXEC (@DropInsertTrigger)
END
IF OBJECT_ID(\'Staging.td_\' + @ObjectName) IS NOT NULL
BEGIN
DECLARE @DropDeleteTrigger nvarchar(max) = \'DROP TRIGGER Staging.td_\' + @ObjectName;
EXEC (@DropDeleteTrigger)
END
END
END
ELSE IF OBJECT_ID(\'Staging.\' + @ObjectName) IS NOT NULL AND OBJECT_ID(\'Archive.\' + @ObjectName) IS NOT NULL
BEGIN
IF @EventType IN (\'CREATE_TABLE\', \'ALTER_TABLE\')
BEGIN
EXEC Staging.CreateStagingTrigger @table_name = @ObjectName;
END
END
END
GO