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

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 2

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;