Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How to Create, Modify or Drop a SQL Server Trigger


By:   |   Last Updated: 2019-05-06   |   Comments (1)   |   Related Tips: More > Triggers

Problem

You are getting your first steps into the SQL Server development world. You know how to write basic queries and stored procedures. Now you want to take advantage of triggers. You understand the concept of what a trigger is and how it works. The problem is that you don’t know how to implement it syntactically. In other words, you don’t know how to create, alter or drop a trigger. In this tip I will help you with that.

Solution

If you never coded an application that uses triggers you may feel nervous the first time working with them. But in this tip, I will walk through the syntax needed to work with triggers.

Working with SQL Server Triggers

There are three statements to use to create, modify or delete triggers.

  • CREATE TRIGGER
  • ALTER TRIGGER
  • DROP TRIGGER

Using the SQL Server CREATE TRIGGER Statement

In the next code section you will see the basic CREATE TRIGGER syntax that you have to use when you want to create a trigger on a table or view. In the case you want to create a trigger into a memory optimized table the following template will work too, but there are some requirements that need to be met that I will explain with the description of the arguments.

CREATE [OR ALTER] TRIGGER trigger_name   
ON { Table name or view name }   
[ WITH <Options> ]  
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] } 
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]
AS
{Your code goes here}  			

Something I want to note is the fact that the [OR ALTER] portion of the CREATE TRIGGER statement was added in SQL Server 2016 so it won’t work in earlier versions. This enhancement allows a more efficient deployment of code because a script containing a CREATE OR ALTER TRIGGER statement won’t fail in case the trigger already exists in the target database.

In the next table, I describe each of the arguments of the CREATE TRIGGER syntax.

Argument Description
WITH <Options> In this argument you can specify additional options for the creation of the trigger. I will cover this further on.
FOR | AFTER | INSTEAD OF Indicates when the trigger must fire when a given event happens, like an insert, update or delete event.
Something you must take into account is the fact that you cannot create an INSTEAD OF trigger on memory optimized tables.
[INSERT], [UPDATE], [DELETE] The DML event (or list of events) that will cause the trigger to fire.
NOT FOR REPLICATION Marks the trigger to not be executed when a replication agent modifies the table that's involved in the trigger.
WITH Option Description Notes
ENCRYPTION Encrypts the Trigger code. Doesn’t work with Memory Optimized Tables.
EXECUTE AS Changes the security context on which the trigger will execute. Required for triggers on memory-optimized tables.
NATIVE_COMPILATION Compiles the trigger code into a binary to make it run natively. Required for triggers on memory-optimized tables.
SCHEMABINDING Ensures that tables that are referenced by a trigger cannot be dropped or altered. Required for triggers on memory-optimized tables.

Now I will show you an example about how to create a trigger, but first we need to set up a test environment.

In this test scenario we have an Employees table with employee’s data and also a column named EmployeeManager which contains the EmployeeID of the Manager of the current employee.

Here is the T-SQL to create the table.

CREATE TABLE Employees(
   EmployeeID int IDENTITY(1,1) NOT NULL,
   EmployeeName varchar(50) NULL,
   EmployeeAddress varchar(50) NULL,
   MonthSalary numeric(10, 2) NULL,
   EmployeeManager int NULL
PRIMARY KEY CLUSTERED (EmployeeID) 
)
GO

Now we will create an AFTER INSERT trigger for the Employees table to check that the value provided for EmployeeManager is a valid EmployeeID in the Employees table. Triggers use two behind the scenes tables Inserted and Deleted to track what the data looks like for new data or for data that has been updated or deleted. In the example below we are using the Inserted table and joining to the Employees table based on the EmployeeID to see if it exists. In addition, we are looking at whether a value was passed for EmployeeManager.

CREATE TRIGGER TR_Employees ON dbo.Employees
AFTER INSERT
AS
 
IF NOT EXISTS (SELECT 0 FROM Inserted I 			
               INNER JOIN dbo.Employees E ON  I.EmployeeManager = E.EmployeeID 
               OR I.EmployeeManager IS NULL) 

BEGIN
   ;THROW 51000, 'Manager must be an active employee', 1;   
END
GO			

Next we will insert some test data as follows:

  • Mark Smith (id=1)
  • Joe Wright (id=2)
    • Manages
      • Mark Hamilton (id=5)
  • John Doe (id=3)
    • Manages
      • Peter Rodriquez (id=4)

I am using the IDENTITY_INSERT so I can control the IDs for these first few records.

SET IDENTITY_INSERT Employees ON 
GO
INSERT Employees (EmployeeID, EmployeeName, EmployeeAddress, MonthSalary, EmployeeManager) 
VALUES (1, N'Mark Smith', N'Ocean Dr 1234', CAST(10000.00 AS Numeric(10, 2)), NULL)
GO
INSERT Employees (EmployeeID, EmployeeName, EmployeeAddress, MonthSalary, EmployeeManager)
VALUES (2, N'Joe Wright', N'Evergreen 1234', CAST(154000.00 AS Numeric(10, 2)), NULL)
GO
INSERT Employees (EmployeeID, EmployeeName, EmployeeAddress, MonthSalary, EmployeeManager) 
VALUES (3, N'John Doe', N'International Dr 1234', CAST(77000.00 AS Numeric(10, 2)), NULL)
GO
INSERT Employees (EmployeeID, EmployeeName, EmployeeAddress, MonthSalary, EmployeeManager) 
VALUES (4, N'Peter Rodriguez', N'74 Street 1234', CAST(45000.00 AS Numeric(10, 2)), 3)
GO
INSERT Employees (EmployeeID, EmployeeName, EmployeeAddress, MonthSalary, EmployeeManager) 
VALUES (5, N'Mark Hamilton', N'Washington Av. 3450', CAST(45000.00 AS Numeric(10, 2)), 2)
GO
SET IDENTITY_INSERT Employees OFF

Finally, we can try our trigger by running the next code that tries to insert two employees into the Employees table. One of the records tries to insert a record for a manager (EmployeeManager value) that doesn’t exist.

-- this insert will work because there is an EmployeeID = 2 record
SELECT * FROM dbo.Employees WHERE EmployeeID = 2

INSERT INTO dbo.Employees (EmployeeName, EmployeeAddress, MonthSalary, EmployeeManager)
VALUES ('Liam Henderson', '65 Street 4444', 95000, 2)

-- this insert will fail because there is not an EmployeeID = 10 record 
SELECT * FROM dbo.Employees WHERE EmployeeID = 10

INSERT INTO dbo.Employees (EmployeeName, EmployeeAddress, MonthSalary, EmployeeManager)
VALUES ('Mattew Lloyd', '47 Street 4444', 95000, 10)
GO			

As you can see on the next screen capture, the first insert worked, but the second did not becuase the trigger prevented us from inserting a row with the non-existing EmployeeID = 10 value for the EmployeeManager column.

The trigger prevented us from inserting the wrong data into the table.

Modifying the Code of a SQL Server Trigger

When you need to change the code of a trigger you can use any of the next methods.

  • Use the ALTER TRIGGER statement
  • Drop and re-create the trigger
  • Use the CREATE OR ALTER statement (Only if your version of SQL Server is greater than SQL Server 2016)

Using the SQL Server ALTER TRIGGER Statement

If you need to change the code of a single trigger maybe the easiest way to do so is by using the ALTER TRIGGER statement. This is because most of the time you can use sp_helptext stored procedure to retrieve the text of the trigger’s code, change the CREATE word to ALTER, write your code and finally hit F5 key in SQL Server Management Studio.

But in such case, if you need to change other aspects of the trigger regardless the trigger code you can use the full syntax of the ALTER TRIGGER statement as shown below.

ALTER TRIGGER trigger_name   
ON { Table name or view name }   
[ WITH <Options> ]  
{ FOR | AFTER | INSTEAD OF }   
{ [INSERT], [UPDATE] , [DELETE] }   
AS
{Your code goes here}  			

Additionally, the next table describes each of the arguments of the ALTER TRIGGER syntax. As you may guess, the arguments are identical to those of the CREATE TRIGGER statement.

Argument Description
WITH <Options> In this argument you can specify additional options for the modification of the trigger. I will cover this further on.
FOR | AFTER | INSTEAD OF /td> Indicates when the trigger must fire when a given event happens, like an insert, update or delete event.
[INSERT], [UPDATE], [DELETE] The DML event (or list of events) that will cause the trigger to fire.
NOT FOR REPLICATION Marks the trigger to not be executed when a replication agent modifies the table that's involved in the trigger.
WITH Option Description Remarks
ENCRYPTION Encrypts the Trigger code. Doesn’t work with Memory Optimized Tables.
EXECUTE AS Changes the security context on which the trigger will execute. Required for triggers on memory-optimized tables.
NATIVE_COMPILATION Compiles the trigger code into a binary to make it run natively. Required for triggers on memory-optimized tables.
SCHEMABINDING Ensures that tables that are referenced by a trigger cannot be dropped or altered. Required for triggers on memory-optimized tables.

Going back to our previous example, now we are going to modify the trigger we created before. The trigger was only validating the data inserted into the table, but it misses data being changed or deleted. Take a look at the following code where we add a validation for those cases.

ALTER TRIGGER TR_Employees ON dbo.Employees
AFTER INSERT, UPDATE, DELETE
AS
 
IF EXISTS (SELECT 0 FROM Inserted)
BEGIN
   IF NOT EXISTS (SELECT 0
                  FROM Inserted I
                  INNER JOIN dbo.Employees E 
                    ON I.EmployeeManager = E.EmployeeID
                    OR I.EmployeeManager IS NULL)
   BEGIN
      ; THROW 51000, 'Manager must be an active employee', 1;
   END;
END 
 
IF EXISTS (SELECT *
           FROM Deleted D
           INNER JOIN dbo.Employees E 
             ON E.EmployeeManager = D.EmployeeID)
BEGIN
   ; THROW 51000, 'You cannot delete a Manager', 1;
END
GO			

After modifying the trigger lets run the next set of code so you can see the trigger doing its job.

The next code attempts to update the employees table for EmployeeID = 2 to set the EmployeeManager to 100, but there is no EmployeeID = 100 in the table.

SELECT * FROM dbo.Employees 
BEGIN TRANSACTION
  PRINT 'Update Result:'
  UPDATE dbo.Employees 
    SET EmployeeManager = 100
    WHERE EmployeeID = 2
ROLLBACK TRANSACTION			

Additionally, the second query tries to delete from the employees table the row with EmployeeID = 2, but that EmployeeID is the manager of EmployeeID = 5.

BEGIN TRANSACTION 
  PRINT 'Delete Result:'
  DELETE FROM dbo.Employees WHERE EmployeeID = 2
ROLLBACK TRANSACTION			

On the next screen capture you can see the output of that query.

Testing the Modified Trigger.

Using the DROP TRIGGER Statement

This is the easiest trigger statement to remember.

DROP TRIGGER [ IF EXISTS ] trigger_name 			

Something I want to note is that the [IF EXISTS] portion of the DROP TRIGGER statement was added in SQL Server 2016, so it won’t work in earlier versions. Basically, this enhancement added to the DROP TRIGGER statement is useful for deploying scripts such that if a trigger does not exist your deployment script will not fail.

For example, if we want to delete the trigger we created earlier we have to run the next statement.

DROP TRIGGER dbo.TR_Employees			

Another interesting fact is that when you delete a table or view that contains triggers, those triggers are also deleted automatically as you can see on the next screen capture.

Dropping a table drops its triggers.
Next Steps


Last Updated: 2019-05-06


get scripts

next tip button



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, May 09, 2019 - 5:25:58 PM - Pedro Back To Top

Hi Daniel,

Very good introductory article. Just a comment about Triggers usage scenarios, they should not be the common procedure when coding business logic.

Triggers is one of those topics that generates a interesting controversy in the community and it is commonly said "Triggers are evil". I won't be so extremist, they are useful in some scenarios, known by experienced DB Devs and DBAs, but for app devs not familiar with them and what involves, it is much safer to avoid them. I commonly say when asked for creating a trigger for adding some code "if you can do it with a SP, use a SP instead".

Here I link a stackoverflow page about the Triggers topic.

Cheers!


Learn more about SQL Server tools