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, 2595🔥, 0💬
Popular Posts:
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
Where to find answers to frequently asked questions on Transaction Management: Commit or Rollback in...
What Is a Parameter File in Oracle? A parameter file is a file that contains a list of initializatio...
How To Verify Your PHP Installation in MySQL? PHP provides two execution interfaces: Command Line In...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...