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

Rolling Back the DDL Statement in a Trigger in SQL Server

Q

Can You Roll Back the DDL Statement in a Trigger in SQL Server?

✍: FYIcenter.com

A

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 and Managing Triggers in SQL Server

⇒⇒SQL Server Database Tutorials

2016-10-22, 812👍, 0💬

Creating a Logon Trigger in Express Edition in SQL Server
Can You Create a Logon Trigger in SQL Server 2005 Express Edition in SQL Server? Can you create a logon trigger in SQL Server 2005 Express Edition? The answer is no. LOGON is not a supported event type in Express Edition. The script below shows you the error message when you try to create a logon tr... 2016-10-22, 731👍, 0💬

"INSTEAD OF" - Overriding DML Statements with Triggers in SQL Server
How To Override DML Statements with Triggers in SQL Server? Sometime, you may want to implement some business logics in a DML trigger to cancel the DML statement. For example, you may want to check the new email address format provided by the UPDATE statement. If the email address is invalid, you to... 2016-10-22, 725👍, 0💬

"CREATE TRIGGER" - Creating a DDL Trigger in SQL Server
How To Create a DDL Trigger using "CREATE TRIGGER" Statements in SQL Server? A DDL trigger is defined to handle a DDL statement event, like create, alter and drop tables, views, indexes, etc. DDL triggers can be used to generate warning messages on database object changes. The format of creating a D... 2016-10-22, 673👍, 0💬

Creating and Managing Schemas in SQL Server
Where to find answers to frequently asked questions on Creating and Managing Schemas in SQL Server? Here is a list of frequently asked questions and their answers compiled by FYIcenter.com DBA team on Creating and Managing Schemas in SQL Server. Clear answers are provided with tutorial exercises on ... 2016-10-22, 639👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.