What are the Triggers in SQL?

Published in

CodingWithYash

4 min readJan 28, 2020

A Trigger is essentially a special type of stored procedure that can be executed in response to one of three conditions
→ An UPDATE
→ An INSERT
→ A DELETE

A Trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs.

For Example → A trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Data Manipulation Language (DML)

→ DELETE
→ INSERT
→ UPDATE

Data Definition Langauge (DDL)

→ CREATE
→ ALTER
→ DROP

Database Operation

→ SERVERERROR
→ LOGON
→ LOGOFF
→ STARTUP
→ SHUTDOWN

Syntax of Trigger :

CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.
{BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating a trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
[OF col_name] − This specifies the column name that will be updated.
[ON table_name] − This specifies the name of the table associated with the trigger.
[REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.
[FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise, the trigger will execute just once when the SQL statement is executed, which is called a table-level trigger.
WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

Types of Triggers :

→DDL Triggers :

In SQL Server we can create triggers on DDL statements (like CREATE, ALTER, and DROP) and the certain system-defined stored procedures that perform DDL like operations.

We can use only AFTER/BEFORE in DDL triggers not INSTEAD OF clause means we can make only ADTER/BEFORE triggers on DDL statements

DDL triggers can be used to observe and control actions performed on the server and to audit these operations. DDL triggers can be used to manage administrative tasks such as auditing and regulating database operations.

→ DML Triggers :

In SQL Server we can create triggers on DML statements (like INSERT, UPDATE and DELETE) and stored procedures that perform DML like operations.

DML Triggers are of two types :

AFTER/BEFORE Trigger (using AFTER/BEFORE clause)
This type of trigger fires after or before SQL Server finishes the execution of action successfully that fired it.
INSTEAD OF Trigger (using INSTEAD OF clause)
This type of trigger fires before SQL Server starts the execution of the action that fixed it. INSTEAD OF trigger replaces the original operation. This differs from the AFTER/BEFORE trigger, which fires after or before the actions that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.

→ LOGON Triggers :

LOGON triggers are a special type of trigger that fires when the LOGON event of SQL Server is raised. This event is raised when a user session is being established with SQL Server that is made after the authentication phase finishes, but before the user session is actually established. Hence all messages that we define in the trigger such as error messages, will be redirected to the SQL Server error log. LOGON triggers do not fire if authentication fails. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login.

Syntax of Login Trigger