Rolling Back the DDL Statement in a Trigger in SQL Server

Q

Can You Roll Back the DDL Statement in a Trigger in SQL Server?

✍: FYIcenter.com

A

Can you roll back the DDL statement in a trigger? The answer is yes. Since the DDL statement that fires the trigger and the statements defined inside the trigger are all executed as a single statement batch, you can add a ROLLBACK statement in the trigger to rollback the entire batch.

USE FyiCenterData;
GO

CREATE TRIGGER drop_rollback ON DATABASE
AFTER DROP_TABLE
AS
  PRINT 'Drop table is not allowed!';
  ROLLBACK;
GO

DROP TABLE fyi_users;
GO
Drop table is not allowed!
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.

This trigger is powerful. It will stop you from dropping any tables in FyiCenterData database.

 

Creating and Managing Triggers in SQL Server

⇒⇒SQL Server Database Tutorials

2016-10-22, 548👍, 0💬