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!!!