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.
2016-10-22, 812👍, 0💬
Popular Posts:
How To Convert Numeric Expression Data Types using the CAST() Function in SQL Server Transact-SQL? I...
How To Locate and Take Substrings with CHARINDEX() and SUBSTRING() Functions in SQL Server Transact-...
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...
How To Import One Table Back from a Dump File in Oracle? If you only want to import one table back t...