How to Use SQL Server Triggers

Triggers are special type of stored procedures that execute as a response to activities in SQL Server objects. Triggers are stored in the system’s memory and are only executed when a specific event occurs. Unlike normal stored procedures, triggers do not accept parameters nor executed manually.

In this article, we will explore how to create and work with triggers in SQL Server.

There are three main types of triggers in SQL Server:

DML Triggers
DDL Triggers
LOGON Triggers

Let us explore these triggers in this guide.

SQL Server DML Triggers

DML or Data Manipulation Language triggers are type of triggers that fire in response to an insert, update and delete operation on a table or view. DML triggers will run when any valid operation is executed, whether any rows are affected.

Create DML After Trigger

In SQL Server, you can create a DML trigger using the create trigger statement.

CREATE TRIGGER schema_name.trigger_name
ON TABLE_NAME
after [INSERT,UPDATE, DELETE]
AS
-- trigger code to run

Let us break down the syntax above:

schema_name – the name of the schema where the trigger is stored.
trigger_name – name of the trigger.
table_name – the name of the table where the specified trigger will apply.
After – an after clause to define under which the conditions the trigger will apply.

To learn how to create and use a DML trigger, let us take a practical example.

Create sample database and insert the data as provided in the set of queries shown below:

-- create sample database
CREATE DATABASE sales;
GO

-- switch db;
USE sales;

-- create table
CREATE TABLE sales(
id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
product_name VARCHAR(50),
price money,
quantity INT
);
-- insert sample data
INSERT INTO sales(product_name, price, quantity)
VALUES ('iPhone Charger', $9.99, 10),
('Google Chromecast', $59.25, 5),
('Playstation DualSense Wireless Controller', $69.00, 100),
('Xbox Series S', $322.00, 3),
('Oculus QUest 2', $299.50, 7),
('Netgear Nighthawk', $236.30, 40),
('Redragon S101', $35.98, 100),
('Star Wars Action Figure', $17.50, 10),
('Mario Kart 8 Deluxe', $57.00, 5);

Once we have the sample data, we can proceed and create a DML trigger to fire upon an update operation on the table.

Consider the example shown below:

-- create table to store update history
CREATE TABLE ModifiedDate (id INT, date_ datetime)
GO
-- create trigger
CREATE TRIGGER dbo.update_trigger
ON sales
after UPDATE
NOT FOR replication
AS
BEGIN
INSERT INTO ModifiedDate
SELECT id, getdate()
FROM inserted
END

The above query will create a trigger that fires when we perform an update on the table. To test trigger, we can run an update as:

-- update table
UPDATE sales SET price = $10.10
WHERE id = 1;

After execution, we can check if the trigger worked by selecting the columns in the ModifiedDate table.

-- check ModifiedDate table
SELECT * FROM ModifiedDate;

In SSMS, you can view the triggers on a table by expanding the triggers option:

Create INSTEAD OF Triggers

The other type of DML triggers in SQL Server is INSTEAD OF triggers. These are type of triggers that execute instead of the DML statement. For example, if we specify a delete statement, we can use the INSTEAD OF triggers to run before the operation.

The syntax for creating an instead of trigger is as shown:

CREATE TRIGGER schema_name.trigger_name
ON TABLE_NAME
instead OF [INSERT, UPDATE, DELETE]
AS
-- trigger statements

For example, the query below creates a trigger that displays a message when an insert operation is performed on the table.

-- create instead of trigger
CREATE TRIGGER instead_insert
ON sales
instead OF INSERT
AS
BEGIN
SELECT 'You cannot insert in this table' AS Error
END

-- run instead_insert trigger
INSERT INTO sales(product_name, price, quantity)
VALUES ('iPhone Charger', $9.99, 10);

Once we run the above query, we should get a message indicating we cannot perform an insert on the table.

Error
-------------------------------

You cannot insert in this table

SQL DDL Triggers

DDL or Data Definition Language are triggers that respond to events to the server or database instead of a table. DDL triggers will respond to events such as DROP, GRANT, DENY, REVOK, UPDATE STATISTICS, CREATE, and ALTER.

Create DDL Triggers

The syntax for create a DDL trigger is as shown:

CREATE TRIGGER trigger_name
ON DATABASE | ALL server
WITH ddl_trigger_parameters
FOR event_type | event_group
AS
-- trigger statements

We can break down the syntax as:

trigger_name – unique name of the trigger.
database or all server – specify where the trigger is executed. Database if it applies on the database or all server if it applies on the server scope.
ddl_trigger_parameter – DDL parameters such as execute as, or encrypt as.
event_type – the DDL event that fires the trigger.

The example query below create a DDL trigger that fires when a DROP table statement is issued.

-- create ddl trigger
CREATE TRIGGER drop_ddl_trigger
ON DATABASE
FOR drop_table
AS
BEGIN
SELECT eventdata();
END

Once we run a drop event on the database, the trigger will display event information using the eventdata() function.

We can test the trigger:

-- test trigger
DROP TABLE sales;

The query should return XML information about the event as:

In SSMS, you can view the triggers by expanding the Database Triggers under Programmability in your target database.

Enable/Disable Triggers

SQL Server allows you to enable and disable triggers. To enable a trigger on a table, use the query as:

enable TRIGGER update_trigger ON sales;

Where update_trigger represent trigger name and sales represent the table name.

You can also enable all triggers on a table as:

enable TRIGGER ALL ON TABLE_NAME;

To enable database trigger, use the query:

enable TRIGGER drop_ddl_trigger ON sales;

Here, drop_ddl_trigger represent the trigger name and sales represent the database.

To enable all database triggers, use the query:

enable TRIGGER ALL ON sales;

To disable a table or database trigger, replace the enable keyword with disable ☺️.

Delete Trigger

To remove a trigger, you can use the drop statement as shown:

DROP TRIGGER IF EXISTS trigger_name;

SQL Server Show All Triggers

To view all the triggers in a SQL Server instance, use the query as shown:

SELECT name,type_desc, is_disabled, is_instead_of_trigger FROM sys.triggers WHERE TYPE = 'TR'

The query should return all the triggers in the SQL Server instance as:

SQL Server LOGON Triggers

Logon triggers are type of triggers that execute when a login activity occurs on the server. These types of triggers run after successful authentication but before creating a user session. Since they are used to handle login activity, we create them at server level, as shown in the example below:

CAUTION: The trigger below may prevent future logins to the server. Ensure to delete before login out.

CAUTION – ☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️.

-- create logon trigger
CREATE TRIGGER login_tg
ON ALL server
FOR logon AS
BEGIN
SELECT 'A trigger after login' AS [message]
END

The trigger will display a message when the user logins into the server.

Conclusion

In this guide, you understood various types of triggers, how to create, enable, disable, delete, and view triggers in SQL Server.