CODEWITHSUNDEEP
sql-server
Steve Henderson
Steve Henderson

Reputation: 514

SQL Server - How to copy data from dropped table when DROP_TABLE TRIGGER fires after table is dropped?

I do not wish to prevent table drops, but when certain tables are dropped in a database I would like to back up either the entire table or query the rows and select specific rows into another table before the drop.

With a normal trigger on a table, if a row was deleted you could access the 'Deleted' table and access those deleted rows.

The DROP_TABLE trigger fires after the table is dropped.

Is there an equivalent to the Deleted table for a DROP_TABLE trigger? Is there a different approach I could use? Or am I going to have to re-code the business logic in the windows service which creates and drops these tables?

(I REALLY don't want to write a trigger which rolls-back the drop, accesses and copies-out the data, then re-drops the table without firing the trigger recursively. I like inventiveness, but this is too mucky a solution for me)

I am running this on Microsoft SQL Server Enterprise Edition (64-bit) and Microsoft SQL Server Developer Edition (64-bit)

Upvotes: 2

Views: 1198

Answers (2)

Steve Henderson
Steve Henderson

Reputation: 514

Thanks for the help guys, but to directly answer my own questions:

  1. For DDL triggers (which fire for DROP TABLE), there is no equivalent to the Deleted table within DELETE/UPDATE triggers

  2. There is no equivalent solution without rolling-back the drop, copying-out the data and re-issuing the drop

  3. The only appropriate and correct approach is to re-code the business logic in the windows service which creates and drops these tables - to permit a soft-delete/move/rename when required

Upvotes: 1

jim31415
jim31415

Reputation: 8808

If it's the recursive firing of the trigger that bothers you, that can be checked for. This will only run for the initial DROP TABLE.

alter Trigger ddlt_ProcessDropTable
on all server for drop_table
AS
begin
    if( trigger_nestlevel() = 1 ) -- only run if top level drop table
    begin
        declare @data XML
        set @data = EVENTDATA()
        -- rollback the drop
        rollback;

        -- get table name
        declare @TableName sysname, @SchemaName sysname, @DataBaseName sysname, @Sql nvarchar(1000);
        select 
            @TableName = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(2000)'),
            @SchemaName = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(2000)'),
            @DataBaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(2000)')

        /****
        Do stuff with the dropped table...
        ****/

        -- re-drop the table
        set @sql = 'Drop Table ' + 
            QuoteName(@DataBaseName) + '.' + QuoteName(@SchemaName) + '.' + QuoteName(@TableName)
        exec(@sql)
    end

end
GO

Upvotes: 0

Related Questions