MS SQL Server Concepts and Programming Question:
Download Job Interview Questions and Answers PDF
Can You Roll Back the DDL Statement in a Trigger?
Answer:
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 GlobalGuideLineDatabase; GO CREATE TRIGGER drop_rollback ON DATABASE AFTER DROP_TABLE AS PRINT 'Drop table is not allowed!'; ROLLBACK; GO DROP TABLE ggl_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 GlobalGuideLineDatabase database.
USE GlobalGuideLineDatabase; GO CREATE TRIGGER drop_rollback ON DATABASE AFTER DROP_TABLE AS PRINT 'Drop table is not allowed!'; ROLLBACK; GO DROP TABLE ggl_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 GlobalGuideLineDatabase database.
Download MS SQL Server Interview Questions And Answers
PDF
| Previous Question | Next Question |
| How To Create a DDL Trigger using "CREATE TRIGGER" Statements? | Can You Create a Logon Trigger in SQL Server 2005 Express Edition? |