Last updated on May 11th, 2023
Estimated reading time: 6 minutes
In SQL, a trigger is a block of code executed automatically in response to a specific event occurred on a table in database.
We can categorize the triggers in SQL Server in mainly three types:
- Data Definition Language (DDL) Triggers
- Data Manipulation Language (DML) Triggers
- Logon Triggers
DML triggers in SQL
DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. DML triggers are frequently used for enforcing business rules and data integrity.
A trigger is invoked either before or after the following event:
- INSERT: when a new row is inserted
- UPDATE:when an existing row is updated
- DELETE: When a row is deleted.
DML Trigger is always associated with a table. Whenever INSERT,UPDATE or DELETE query is fired on the table corresponding trigger code is executed.
CREATE/ALTER triggers syntax in SQL
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name
ON { table }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement [ ; ] [ ,...n ] }
<dml_trigger_option> ::=
[ NATIVE_COMPILATION ]
[ SCHEMABINDING ]
[ EXECUTE AS Clause ] schema_name: The name of the schema to which a DML trigger belongs. DML triggers are scoped to the schema of the table or view on which they’re created.
trigger_name: The name of the trigger
table | view: The table or view on which the DML trigger runs.
NATIVE_COMPILATION: Indicates that the trigger is natively compiled. This option is required for triggers on memory-optimized tables.
SCHEMABINDING: Ensures that tables referenced by a trigger can’t be dropped or altered.
FOR | AFTER: FOR or AFTER specifies that the DML trigger fires only when all operations specified in the triggering SQL statement have launched successfully.
Sql_statement:The trigger conditions and actions. Trigger conditions specify additional criteria that determine whether the tried DML, DDL, or logon events cause the trigger actions to be run.
The following Transact-SQL statements aren’t allowed in a DML trigger:
- ALTER DATABASE
- CREATE DATABASE
- DROP DATABASE
- RESTORE DATABASE
- RESTORE LOG
- RECONFIGURE
DML Triggers Examples
If you are new to SQL refer our articles on SQL Basics Archives – Full Stack Tutorials Hub
Employee Table has schema as below.
USE [Hrms]
GO
/****** Object: Table [dbo].[Employee] Script Date: 10-04-2023 14:28:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](256) NULL,
[LastName] [varchar](256) NULL,
[Email] [varchar](256) NULL,
[Street] [varchar](256) NULL,
[Zip] [varchar](256) NULL,
[City] [varchar](256) NULL,
[PhoneNumber] [varchar](256) NULL,
[Comment] [varchar](256) NULL
) ON [PRIMARY]
GO
//Crate a new Audit log table for employee
USE [Hrms]
GO
/****** Object: Table [dbo].[Employee] Script Date: 10-04-2023 14:12:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EmployeeAudit](
[Id] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int],
[FirstName] [varchar](256) NULL,
[LastName] [varchar](256) NULL,
[Email] [varchar](256) NULL,
[Street] [varchar](256) NULL,
[Zip] [varchar](256) NULL,
[City] [varchar](256) NULL,
[PhoneNumber] [varchar](256) NULL,
[Comment] [varchar](256) NULL,
[Updatedtime] datetime null,
) ON [PRIMARY]
GO
USE [HRMS]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[trg_EmployeeInsertUpdate]
on [dbo].[Employee]
for insert, update
as
begin
SET NOCOUNT ON
update dbo.EmployeeAudit
set Updatedtime = GETDATE()
from inserted i
where dbo.EmployeeAudit.EmployeeId = i.EmployeeId
insert into dbo.EmployeeAudit(
EmployeeId,
FirstName ,
LastName ,
Email ,
Street ,
Zip ,
City,
PhoneNumber,
Comment
)
select
EmployeeId,
FirstName ,
LastName ,
Email ,
Street ,
Zip ,
City,
PhoneNumber,
Comment
from inserted
end
GO
Whenever a new entry is added, or existing record is updated in Employee Table trg_EmployeeInsertUpdate trigger would fire to update these changes in Employee Audit Table.
insert into Employee(FirstName,LastName,Email,Street,Zip,City)
values('Deepti','Sawant','Deepti.sawant@gmail.com','mumbai','400069','Mumbai')
When record is deleted in employee table. Deleted timestamp is updated in Audit log table.
USE [Hrms]
GO
/****** Object: Trigger [dbo].[trg_ProjectCostDelete] Script Date: 10-04-2023 14:35:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE trigger [dbo].[trg_EmployeeDelete]
on [dbo].[Employee]
for delete
as
begin
SET NOCOUNT ON
update dbo.EmployeeAudit
set Updatedtime =GETDATE()
from deleted d
where dbo.EmployeeAudit.EmployeeId = d.EmployeeId
end
GO
DDL Triggers
DDL triggers are fired in response to the DDL events, such as CREATE, ALTER, and DROP statements. We can create these triggers at the database level or server level, depending on the type of DDL events
DDL triggers, like standard triggers, launch stored procedures in response to an event. But, unlike standard triggers, they don’t run in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they primarily run in response to data definition language (DDL) statements. The statement types include CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS. Certain system stored procedures that carry out DDL-like operations can also fire DDL triggers.
LOGON Triggers
Logon triggers are fires in response to a LOGON event.This event happens when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is established. So, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log
Logon triggers don’t fire if authentication fails.
How to SHOW Triggers in SQL Server ?
When we have multiple databases with multiple tables, the show or list trigger would be very helpful.
Using the following command, we can see a list of all the triggers available in SQL Server:
SELECT name, is_instead_of_trigger
FROM sys.triggers
WHERE type = 'TR'; How to UPDATE Triggers in SQL Server?
Triggers can be altered either by using SSMS or by using T-SQL . Navigate to the table trigger and click modify.
ALTER TRIGGER can be used to modify existing trigger.
USE [Hrms]
GO
/****** Object: Trigger [dbo].[trg_EmployeeInsertUpdate] Script Date: 10-04-2023 14:57:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[trg_EmployeeInsertUpdate]
on [dbo].[Employee]
for insert, update
as
begin
SET NOCOUNT ON
update dbo.EmployeeAudit
set Updatedtime = GETDATE()
from inserted i
where dbo.EmployeeAudit.EmployeeId = i.EmployeeId
insert into dbo.EmployeeAudit(
EmployeeId,
FirstName ,
LastName ,
Email ,
Street ,
Zip ,
City,
PhoneNumber,
Comment
)
select
EmployeeId,
FirstName ,
LastName ,
Email ,
Street ,
Zip ,
City,
PhoneNumber,
Comment
from inserted
endHow to DELETE Triggers in SQL Server?
Existing trigger in SQL Server can be deleted using the DROP TRIGGER statement.ment.Once deleted
DROP TRIGGER [IF EXISTS] schema_name.trigger_name; The IF EXISTS option allows you to delete a trigger if the trigger exists. If the trigger does not exist, then the statement does nothing. However, if you don’t have the IF EXISTS option, the database system may issue an error if you try to drop a non-existing trigger.
SQL trigger usages
Enforce complex integrity of data. In this scenario, you may define triggers to validate the data and reformat the data if necessary. For example, you can transform the data before insert or update using a BEFORE INSERT or BEFORE UPDATE trigger.
Triggers help us to keep a log of records.
Disadvantages of Triggers
Triggers are invoked automatically, and their execution is invisible to the user. Therefore, it isn’t easy to troubleshoot what happens in the database layer.
We can create a trigger in the current database only, but it can reference objects outside the current database.