/******************************************************
 *
 * Name:         manage-table-triggers.sql
 *     
 * Design Phase:
 *     Author:   John Miner
 *     Date:     12-27-2017
 *     Blog:     www.craftydba.com
 *
 *     Purpose:  
 *       Dynamically create table triggers for data auditing.
 *
 ******************************************************/


-- Delete existing procedure
DROP PROCEDURE IF EXISTS [audit].[manage_table_triggers]
GO


-- Create new procedure
CREATE PROCEDURE [audit].[manage_table_triggers]
    @target_schema sysname = 'active',
	@command_action varchar(16) = 'create',
	@verbose_flag int = 0
AS
BEGIN
    -- Local variables
	DECLARE @my_schema_name sysname;
	DECLARE @my_table_name sysname;
    DECLARE @var_tsql nvarchar(2048);

    -- List non audit tables
    DECLARE var_cursor CURSOR FOR    
        SELECT s.name as my_schema_name, t.name as my_table_name
        FROM sys.tables t join sys.schemas s on t.schema_id = s.schema_id
        WHERE s.name = @target_schema;

    -- Open cursor
    OPEN var_cursor;

    -- Get first row
    FETCH NEXT FROM var_cursor 
        INTO @my_schema_name,  @my_table_name;

    -- While there is data
    WHILE (@@fetch_status = 0)
    BEGIN

	    -- Show the values
		/*
		PRINT @my_schema_name
		PRINT @my_table_name
		*/

		-- Remove existing trigger
		SET @var_tsql = '';
		SET @var_tsql += '-- Drop trigger' + CHAR(13)
		SET @var_tsql += 'IF OBJECT_ID(''['+ @my_schema_name + '].[trg_ltc_' + @my_table_name + ']'') IS NOT NULL ' + CHAR(13) 
		SET @var_tsql += '    DROP TRIGGER ['+ @my_schema_name + '].[trg_ltc_' + @my_table_name + '];';

		-- Process drop command
		IF (@command_action = 'create') OR (@command_action = 'drop')
		BEGIN

    		-- Verbose messaging
	    	IF (@verbose_flag = 1)
		    BEGIN
		        PRINT @var_tsql;
		        PRINT '';
		    END

            -- Execute dynamic code
			EXEC(@var_tsql);
        END

		-- Create new trigger
		SET @var_tsql = '';
		SET @var_tsql += '-- Create trigger' + CHAR(13)
		SET @var_tsql += 'CREATE TRIGGER ['+ @my_schema_name + '].[trg_ltc_' + @my_table_name + '] '
		SET @var_tsql += 'ON ['+ @my_schema_name + '].[' + @my_table_name + '] ' + CHAR(13)
		SET @var_tsql += 'FOR INSERT, UPDATE, DELETE AS ' + CHAR(13)
		SET @var_tsql += 'BEGIN ' + CHAR(13) + CHAR(13)

		SET @var_tsql += '-- Detect inserts' + CHAR(13)
		SET @var_tsql += 'IF EXISTS (select * from inserted) AND NOT EXISTS (select * from deleted)' + CHAR(13)
		SET @var_tsql += 'BEGIN' + CHAR(13)
		SET @var_tsql += '    INSERT [audit].[log_table_changes] ([chg_type], [schema_name], [object_name], [xml_recset])' + CHAR(13)
		SET @var_tsql += '    SELECT ''INSERT'', ''[' +  @my_schema_name + ']'', ''[' + @my_table_name + ']'',' 
		SET @var_tsql += ' (SELECT * FROM inserted as Record for xml auto, elements , root(''RecordSet''), type)' + CHAR(13)
		SET @var_tsql += '    RETURN;' + CHAR(13)
		SET @var_tsql += 'END' + CHAR(13) + CHAR(13)

		SET @var_tsql += '-- Detect deletes' + CHAR(13)
		SET @var_tsql += 'IF EXISTS (select * from deleted) AND NOT EXISTS (select * from inserted)' + CHAR(13)
		SET @var_tsql += 'BEGIN' + CHAR(13)
		SET @var_tsql += '    INSERT [audit].[log_table_changes] ([chg_type], [schema_name], [object_name], [xml_recset])' + CHAR(13)
		SET @var_tsql += '    SELECT ''DELETE'', ''[' +  @my_schema_name + ']'', ''[' + @my_table_name + ']'',' 
		SET @var_tsql += ' (SELECT * FROM deleted as Record for xml auto, elements , root(''RecordSet''), type)' + CHAR(13)
		SET @var_tsql += '    RETURN;' + CHAR(13)
		SET @var_tsql += 'END' + CHAR(13) + CHAR(13)

		SET @var_tsql += '-- Detect updates' + CHAR(13)
		SET @var_tsql += 'IF EXISTS (select * from inserted) AND EXISTS (select * from deleted)' + CHAR(13)
		SET @var_tsql += 'BEGIN' + CHAR(13)
		SET @var_tsql += '    INSERT [audit].[log_table_changes] ([chg_type], [schema_name], [object_name], [xml_recset])' + CHAR(13)
		SET @var_tsql += '    SELECT ''UPDATE'', ''[' +  @my_schema_name + ']'', ''[' + @my_table_name + ']'',' 
		SET @var_tsql += ' (SELECT * FROM deleted as Record for xml auto, elements , root(''RecordSet''), type)' + CHAR(13)
		SET @var_tsql += '    RETURN;' + CHAR(13)
		SET @var_tsql += 'END' + CHAR(13) + CHAR(13)

		SET @var_tsql += 'END; ' + CHAR(13)
	

		-- Process create command
		IF (@command_action = 'create')
		BEGIN

    		-- Verbose messaging
	    	IF (@verbose_flag = 1)
		    BEGIN
		        PRINT @var_tsql;
		        PRINT '';
		    END

            -- Execute dynamic code
			EXEC(@var_tsql);
        END


		-- Get next row
        FETCH NEXT FROM var_cursor 
        INTO @my_schema_name,  @my_table_name;

	END

    -- Close cursor
    CLOSE var_cursor;

    -- Release memory
    DEALLOCATE var_cursor;

END;
GO

/*

-- Add triggers to tables
EXEC [audit].[manage_table_triggers] @target_schema = 'active', @command_action = 'create', @verbose_flag = 1
GO

*/

/*
-- Show the triggers
SELECT 
    s.name,
	tr.name,
	tr.type_desc,
	tr.is_instead_of_trigger
FROM 
    sys.triggers tr join sys.tables t on tr.parent_id = t.object_id 
	join sys.schemas s ON t.schema_id = s.schema_id
WHERE s.name = 'active'
GO

*/