SQL Server DDL trigger on drop table

Question

I have a database with few tables: tab1, tab2, tab3. How can I create a DDL trigger on drop table only for tab2 (not for tab1 and tab3). When drop tab2 is called I need to update values in this table but not to delete tab2. How can I do this? I found this but don't understand how it works:

create trigger trDatabse_OnDropTable
on database
for drop_table
as
begin
 set nocount on;
 select
  'Table dropped: ' +
  quotename(eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname')) + N'.' +
  quotename(eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'));
end;

Thanks!

Answer 1

In this article DDL Triggers you'll find that there are no INSTEAD OF DDL Triggers, like in DML Triggers, so you can't prevent the drop and execute your own command instead:

DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers do not fire in response to events that affect local or global temporary tables and stored procedures.

This piece of code you posted is for logging DROP TABLE event. eventdata() contains XML with some information on the event: LoginName, ObjectName, TSQLCommand, PostTime and many more.

Answer 2

You can prevent deletion of a table with ddl trigger. Something like:

CREATE TRIGGER drop_safe 
ON DATABASE 
FOR DROP_TABLE 
AS 
   PRINT 'You must disable Trigger "drop_safe" to drop table!' 
   ROLLBACK
;

To only prevent deletion on your specified table you have to look at the eventdata() and ROLLBACK only in that special case.

Answer 3

create trigger trDatabse_OnDropTable
on database
for drop_table
as
begin
    set nocount on;

    --Get the table schema and table name from EVENTDATA()
    DECLARE @Schema SYSNAME = eventdata().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
    DECLARE @Table SYSNAME = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');

    IF @Schema = 'dbo' AND @Table = 'tab2'
    BEGIN
        PRINT 'DROP TABLE Issued.';

        --Optional: error message for end user.
        RAISERROR ('[dbo].[tab2] cannot be dropped.', 16, 1);

        --Rollback transaction for the DROP TABLE statement that fired the DDL trigger
        ROLLBACK;

        --Run your update after the ROLLBACK
        BEGIN TRAN
            UPDATE dbo.tab2
            SET ... ;
        COMMIT;
    END
    ELSE
    BEGIN
        --Do nothing.  Allow table to be dropped.
        PRINT 'Table dropped: [' + @Schema + '].[' + @Table + ']';
    END
end;