DOT NET RULES

Looking into Triggers in SQL Server October 2, 2011

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.

You can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

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)[1]','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)[1] ',
'nvarchar(25) ') AS DATETIME) ,
@HostName = @Data.value(' (/EVENT_INSTANCE/HostName)[1] ',
'nvarchar(25) ') ,
@LoginName = @Data.value(' (/EVENT_INSTANCE/LoginName)[1] ',
'nvarchar(25) ') ,
@Command = @Data.value(' (/EVENT_INSTANCE/TSQLCommand)[1] ',
'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!!!