Collections:
Rolling Back the DDL Statement in a Trigger in SQL Server
Can You Roll Back the DDL Statement in a Trigger in SQL Server?
✍: FYIcenter.com
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 a Logon Trigger in Express Edition in SQL Server
⇐ "CREATE TRIGGER" - Creating a DDL Trigger in SQL Server
2016-10-22, 2607🔥, 0💬
Popular Posts:
Can Date and Time Values Be Converted into Integers in SQL Server Transact-SQL? Can date and time va...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
Where to find answers to frequently asked questions on Downloading and Installing SQL Server 2005 Ex...