Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Rolling Back the DDL Statement in a Trigger

By: FYIcenter.com

(Continued from previous topic...)

Can You Roll Back the DDL Statement in a Trigger?

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.

(Continued on next topic...)

  1. What Are Triggers?
  2. What Are the Basic Features of a Trigger?
  3. How To Create a Simple Table to Test Triggers?
  4. How To Create a DML Trigger using CREATE TRIGGER Statements?
  5. How To Test a DML Trigger?
  6. How To List All Triggers in the Database with sys.triggers?
  7. How To Modify Existing Triggers using "ALTER TRIGGER"?
  8. How To Delete Existing Triggers using "DROP TRIGGER"?
  9. How To Get the Definition of a Trigger Back?
  10. How To Disable Triggers using "DISABLE TRIGGER"?
  11. How To Create a Trigger for INSERT Only?
  12. How To See the Event List of an Existing Trigger using sys.trigger_events?
  13. How To Access the Inserted Record of an Event?
  14. How To Access the Deleted Record of an Event?
  15. How To Improve the Trigger to Handle NULL Values?
  16. What Happens to a Trigger with Multiple Affected Rows?
  17. How To Override DML Statements with Triggers?
  18. How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
  19. Can You Roll Back the DDL Statement in a Trigger?
  20. Can You Create a Logon Trigger in SQL Server 2005 Express Edition?

Related Articles:


Other Tutorials/FAQs:


Related Resources:


Selected Jobs: