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, 2712🔥, 0💬
Popular Posts:
How To Convert Characters to Numbers in Oracle? You can convert characters to numbers by using the T...
What Are the Underflow and Overflow Behaviors on FLOAT Literals in SQL Server Transact-SQL? If you e...
What is sqlservr.exe - Process - SQL Server (SQLEX?PRESS) in SQL Server? Process sqlservr.exe is the...
How to change the data type of an existing column with "ALTER TABLE" statements in SQL Server? Somet...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...