How to Create, Modify or Drop a SQL Server Trigger

By: Daniel Farina   |   Updated: 2019-05-06   |   Comments (1)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > 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.

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.

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.

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.

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.

Next Steps

• If you found it hard to understand the usage of the inserted and deleted tables take a look at this tip: Understanding SQL Server inserted and deleted tables for DML triggers.
• For an example of an INSTEAD OF trigger you can check out this tip: Using INSTEAD OF triggers in SQL Server for DML operations.
• In case you don’t know why I used a THROW statement in the trigger I suggest you to read the next tip: SQL Server 2012 THROW statement to raise an exception.
• You can see another example of a trigger in the next tip: SQL Server Trigger Example.
• Take a look at my previous tip SQL Server Referential Integrity across Databases Using Triggers.
• Stay tuned to SQL Server Triggers Tips Category for more tips and tricks.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips