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!

I don't think you can prevent a `DROP` using a trigger, just react to it having been done (ie just an `AFTER` trigger) — Joachim Isaksson, Mar 10 '14 at 22:13
@JoachimIsaksson: guess you'd have to `DENY` the permission to `DROP` to prevent this from happening .... — marc_s, Mar 10 '14 at 22:14
@marc_s Yes, that'd prevent the drop, but also probably prevent the trigger from firing to do the updates. — Joachim Isaksson, Mar 10 '14 at 22:18
You **can** prevent a `DROP TABLE` statement using a DDL Trigger. As noted in the answer from @Sven, DDL Trigger events can be rolled back. The use of a `DENY` permission would not be needed. — Dave Mason, Jul 01 '16 at 17:19

score 3 · Answer 1 · answered Dec 10 '15 at 15:20

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.

Dave Mason · Answer 2 · 2016-07-01T19:44:28.110

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;

score 1 · Accepted Answer · answered Dec 29 '14 at 11:37

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.

Actually you can prevent the `DROP TABLE` command. DDL events associated with DDL triggers are transacted operations and can be rolled back. @Sven shows this in his answer. — Dave Mason, Dec 07 '16 at 20:07

SQL Server DDL trigger on drop table

3 Answers3