DDL Triggers are AFTER triggers December 24, 2016Posted by fofo in Sql Server, Sql Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016.
Tags: AFTER triggers, DDL triggers
add a comment
In this post I would like to look into DDL triggers and explain their functionality.
Let me start with a short introduction on triggers
All SQL Server developers use SQL triggers that basically is a mechanism that is invoked when a particular action occurs on a particular table.
Triggers consist of
- A name
- An action
- The execution
The maximum size of a trigger name is 128 characters.
The action of a trigger can be either a DML statement (INSERT, UPDATE, or DELETE) or a DDL statement (CREATE, ALTER, DROP)
Therefore, there are two trigger forms: DML triggers and DDL triggers.
The AFTER and INSTEAD OF options are two additional options that you can define for a trigger.
- AFTER triggers fire after the triggering action occurs.
- INSTEAD OF triggers are executed instead of the corresponding triggering action.
- AFTER triggers can be created only on tables, while INSTEAD OF triggers can be created on both tables and views
DDL triggers were introduced in SQL Server 2005. DDL triggers are not INSTEAD OF triggers.
They are implemented as AFTER triggers, which means the operation occurs and is then caught in the trigger.
The the operation can be optionally rolled-back, if you put a ROLLBACK statement in the trigger body.
This means they’re not quite as lightweight as you might think. Imagine doing the following:
ALTER TABLE MyTable ADD newcolumn VARCHAR (30) DEFAULT ‘default value’;
If we have a DDL trigger defined for ALTER_TABLE events, or DDL_TABLE_EVENTS, the trigger due to the above T-SQL batch will fire and every row in the table will be expanded to include the new column (as it has a non-null default), and the operation is rolled back by your trigger body.
Type (copy paste) the following T-SQL statements in a new query window in the SSMS
CREATE DATABASE sampleDB; GO USE sampleDB; GO CREATE TABLE Product ( Pid INT PRIMARY KEY IDENTITY , pname NVARCHAR(50) , price DECIMAL(18, 4) ); CREATE TRIGGER Not_Alter_Tables ON DATABASE FOR ALTER_TABLE AS PRINT 'Not Alter Statements are permitted on the tables of this DB' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)', 'nvarchar(max)') RAISERROR ('Tables cannot be altered in this database.', 16, 1) ROLLBACK; GO ALTER TABLE dbo.Product ADD newcolumn VARCHAR (30) DEFAULT 'default value';
Let me explain what I do in the code above.
I create a sample database. I create sample empty table. I create a trigger “Not_Alter_tables” on this database. This trigger applies to all the tables in the database.
This trigger is a DDL trigger, hence an AFTER trigger. In the trigger I capture the T-SQL statement that invoked the trigger, I print a statement, raise an error and then rollback the operation.
Then I attempt to add a new column in the table.
In this example, when the trigger is invoked, the table will be expanded to include the new column (as it has a non-null default), and then the operation is rolled in the trigger body.
Have a look at the picture below.
In a nutshell DDL triggers are AFTER triggers and can be quite expensive. In the example above the best way is not to use DDL triggers but to use instead explicit permissions e.g REVOKE/DENY to prevent users from altering the schema of the table.
Hope it helps!!!
Looking into Triggers in SQL Server October 2, 2011Posted by fofo in Sql Server.
Tags: AFTER triggers, DDL triggers, DML triggers, INSTEAD OF triggers, Logon triggers, triggers
In this post I would like to show a few examples of how to use triggers in SQL Server. I would like to talk about the uses of triggers, the type of triggers and some misconceptions about the triggers.
I know there are samples of how to use triggers in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.
Many people have found useful my style of posting. They feel they can follow the steps and master the technology.
Before we move on, let’s look into trigger types,uses and definitions.
A trigger is a mechanism that in invoked when a particular action occurs on a particular table or view. Each trigger has three general parts:
- a name
- the action
- the execution
The action of a trigger can be either a DML statement (INSERT,UPDATE,DELETE) or a DDL statement (CREATE,ALTER,DROP). So we have two types of triggers:DML and DDL triggers.There are more options when defining triggers. We have the AFTER and INSTEAD OF options.AFTER triggers fire after the triggering action occurs while INSTEAD OF triggers are executed instead of the corresponding triggering action.AFTER triggers can be created only on tables.INSTEAD OF triggers can be created on both tables and views.Triggers on Views only fire if data changes through a view.We cannot directly execute them.Triggers can be thought as procedures that run in response to changes to data. Their main purpose is to determine whether to commit or rollback tha changes. You can think of them as a variation of stored procedures. They are treated the same internally by SQL Server as far parsing,name resolution and query plan otimisation is concerned.When it comes to performance, I have one sentence for you guys “Triggers are expensive”.
Whenever you can use constraints or referential integrity (cascade updates and deletes) to enforce data integrity you should do that. Constraints fire before data modification. AFTER triggers fire after data modification.Triggers should be only used to enforce complex business rules.Another useful operation triggers can perform is that they can record changes to data. They cannot create objects, they cannot modify the schema of an object(e.g add a column to a table) and cannot perform administrative tasks (e.g backups).
Finally, I must point out that triggers use virtual tables, inserted and deleted. INSERT statements put new rows in inserted virtual table. UPDATE statements put new values in inserted virtual table and old values in deleted virtual table. DELETE statements put affected rows in deleted virtual tables.
Well, you might be confused with all these options I have mentioned but do not worry I will provide you with examples and you will not have any questions,hopefully.
We need an instance of SQL Server running in our machine. I have SQL Server 2008 R2 Developer edition installed in my machine.You can use any edition of SQL Server you might have installed in your machine.We need a database as well. I will use the Northwind database.
1) Launch SQL Server Management Studio and connect to the local instance of SQL Server.
Let’s write our first trigger.I am going to create table (Football) in Northwind database. Then I will insert some values in it. Then I will create an AFTER trigger where I will check whether the user tries to delete the row where the TeamName field equals “Liverpool”.If that happens then the transaction should be rolled back. Have a look at the T-SQL code below.
USE Northwind GO CREATE TABLE dbo.Football ( TeamID INT PRIMARY KEY , TeamName VARCHAR(50) NOT NULL , TeamCoach VARCHAR(50) NOT NULL , TeamCaptain VARCHAR(50) NOT NULL ) ; GO INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain) VALUES (1,'ManUtd','Alex Ferguson','Nemanja Vidić'); INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain) VALUES (2,'Liverpool','Kenny Dalglish','Steven Gerrard'); INSERT INTO dbo.Football(TeamID,TeamName,TeamCoach,TeamCaptain) VALUES (3,'Everton','David Moyes','Phil Neville'); -- Create the trigger CREATE TRIGGER dbo.NoDeleteLiverpool ON dbo.Football FOR DELETE AS DECLARE @FootballValue varchar(50); SELECT @FootballValue = TeamName FROM deleted; IF @FootballValue = 'Liverpool' BEGIN PRINT 'Deleting Liverpool data is not allowed.'; ROLLBACK TRAN; END GO DELETE FROM dbo.Football WHERE TeamName='Liverpool'; GO --delete the trigger DROP TRIGGER dbo.NoDeleteLiverpool DROP TABLE dbo.football
Run the code and see the results. In the last T-SQL statement above I check if the trigger works by executing a Delete statement.. If you run the Delete statement the trigger will fire and you will receive a message like this
“Deleting Liverpool data is not allowed.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.”
Finally I drop the trigger and then drop the table as well.
So we have created an AFTER delete trigger. Now I can create an INSTEAD OF trigger that will check if someone is trying to delete any rows from the Categories table. If that happens then the transaction must be rolled back.A message should be printed to the user as well.
USE Northwind go CREATE TRIGGER NoCategoriesDelete ON dbo.Categories INSTEAD OF DELETE AS BEGIN DECLARE @Count INT SET @Count = @@ROWCOUNT IF @Count = 0 RETURN SET NOCOUNT ON BEGIN RAISERROR (N'Categories cannot be deleted. They can only be marked as not current.', -- Message 10, -- Severity. 1) -- State. -- Rollback any active or uncommittable transactions IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION END END END DELETE FROM dbo.Categories WHERE CategoryName='Beverages' SELECT * FROM dbo.Categories DROP TRIGGER NoCategoriesDelete
Run the query and see the results.
Now let’s create another trigger. This is going to be an AFTER INSERT trigger . I will use this trigger to check that only a number of characters more than seven will be inserted in the RegionDescription column of the dbo.Region column.The code follows
USE Northwind go CREATE TRIGGER RegionLength ON dbo.Region AFTER INSERT AS BEGIN DECLARE @RegionDescription nvarchar(50) SELECT @RegionDescription = RegionDescription FROM inserted IF len(@RegionDescription) < 7 BEGIN ROLLBACK TRANSACTION END END --this will fail INSERT INTO dbo.Region VALUES (6,'London') --this will succeed INSERT INTO dbo.Region VALUES (6,'South London') --drop the trigger DROP TRIGGER RegionLength
Run the query and see the results.As I said earlier you should always use CHECK constraints instead of triggers when possible.In this case, we will alter the definition of the table and we add a CHECK constraint.So we do not need the trigger anymore, that is why I deleted above.In the code below I create the CHECK constraint.
USE [Northwind] GO ALTER TABLE [dbo].[Region] WITH NOCHECK ADD CONSTRAINT [CK_RegionDescriptionLength] CHECK ((len([RegionDescription])>(7))) GO ALTER TABLE [dbo].[Region] CHECK CONSTRAINT [CK_RegionDescriptionLength] GO
Let’s see some examples of DDL triggers. They were introduced in SQL Server 2005.DDL triggers are implemented as AFTER triggers.That means that the operation that fires the trigger actually takes place and then the trigger is activated and rolls back the operation if we put a ROLLBACK statement in the trigger body.In this example below, I will show you how to create a trigger that does not allow to create new tables in the Northwind database.The event I am interested is (CREATE_TABLE ). Have a look at MSDN and BOL for the DDL events that you can write triggers for.
USE Northwind GO CREATE TRIGGER NoNewTablesAreAllowed ON DATABASE FOR CREATE_TABLE AS PRINT 'No new tables allowed!!!!!.You will soon hear from the DBA!!!'; ROLLBACK ; GO -- Test the trigger -- you will get an error CREATE TABLE dbo.MyTable (col1 varchar(50),col2 VARCHAR(50),col3 VARCHAR(50)); GO -- Disable the trigger. DISABLE TRIGGER NoNewTablesAreAllowed ON DATABASE; GO -- Now attempt to create the table again. You will succeed CREATE TABLE dbo.MyTable (col1 varchar(50),col2 VARCHAR(50),col3 VARCHAR(50)); GO DROP TRIGGER NoNewTablesAreAllowed ON DATABASE DROP TABLE dbo.MyTable
Run the code and see the results.
Now we will create another DDL trigger. In this case we do not want to allow anyone to create a database on the server.We also want to print on the results the statement that the user typed when he attempted to create the database (e.g CREATE DATABASE MYDB). We will use the EVENTDATA() function that returns data only when appears in the body of a DDL or logon trigger. Basically it returns information about server and database events.The event I am interested in is (FOR CREATE_DATABASE).When the user types a “create database” statement the trigger will fire and print a message to the screen and rollback the transaction.
USE master GO IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'ddl_trig_database') DROP TRIGGER ddl_trig_database ON ALL SERVER; GO CREATE TRIGGER ddl_trig_database ON ALL SERVER FOR CREATE_DATABASE AS PRINT 'You are trying to create a new database on the server .you should have checked with me first !!!' SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)','nvarchar(max)') rollback GO --try to create a database. It will fail CREATE DATABASE MytestDB --drop the trigger DROP TRIGGER ddl_trig_database ON ALL SERVER; GO --this will succeed now CREATE DATABASE MytestDB --drop database DROP DATABASE MytestDB
Run the code and see the results.
We can also use triggers for auditing purposes.First we need to create a table that we will store the audit data. We need to have columns that will store data like the login name, the command and the time the command was executed.Then we need to create the trigger that will populate the table with information about all database level events of the Northwind database.The event type and Transact-SQL statement are captured by using XQuery expressions against the XML data generated by EVENTDATA() function.Make sure you run the code below as is defined in the steps(in the comments).
Have a look at the code below.
-- STEP 1, Create DDL Audit table USE Northwind GO CREATE TABLE DatabaseDDLAudit (AuditID int IDENTITY(1,1) NOT NULL ,PostTime datetime NOT NULL ,LoginName nvarchar(128) NULL ,Command nvarchar(MAX) NULL ,EventData xml NULL) -- STEP 2, Create DDL database trigger CREATE TRIGGER NoDDLAllowed ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS SET NOCOUNT ON DECLARE @data AS XML , @PostTime AS DATETIME , @HostName AS NVARCHAR(128) DECLARE @LoginName AS NVARCHAR(128) , @Command AS NVARCHAR(MAX) SET @data = EVENTDATA() SELECT @PostTime = CAST(@Data.value(' (/EVENT_INSTANCE/PostTime) ', 'nvarchar(25) ') AS DATETIME) , @HostName = @Data.value(' (/EVENT_INSTANCE/HostName) ', 'nvarchar(25) ') , @LoginName = @Data.value(' (/EVENT_INSTANCE/LoginName) ', 'nvarchar(25) ') , @Command = @Data.value(' (/EVENT_INSTANCE/TSQLCommand) ', 'nvarchar(max) ') RAISERROR ('Modifications to this database are not allowed!', 16, 1) ROLLBACK INSERT DatabaseDDLAudit (PostTime, LoginName, Command, EventData) VALUES (@PostTime, @LoginName, @Command, @Data) RETURN; SELECT * from databaseddlaudit -- STEP 3, Test the trigger ALTER TABLE dbo.Categories ADD NewColumn varchar(10) NULL; -- Step 4, View DDL Audit SELECT * FROM DatabaseDDLAudit; GO --step 5 DISABLE TRIGGER NoDDLAllowed ON DATABASE; GO CREATE TABLE TestTable (Column1 int ,Column2 int); GO DROP table testtable --step 6 ENABLE TRIGGER NoDDLAllowed ON DATABASE; drop TRIGGER NoDDLAllowed ON DATABASE; drop TABLE DatabaseDDLAudit
We have covered a lot of things regarding triggers and you can follow all the examples in this post. They are very easy to follow.
Hope its helps!!!