By: Daniel Farina | 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.
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)
- Manages
- John Doe (id=3)
- Manages
- Peter Rodriquez (id=4)
- Manages
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.
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.
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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips