Triggers in SQL

Introduction

In SQL, triggers represent a powerful feature that automates specific actions within a database. Imagine a scenario where you're keenly monitoring a database table, waiting for a specific column to update. While repeatedly querying the table at intervals might be your initial strategy, there are more efficient options. Triggers offer a more elegant solution by automatically executing stored procedures—functions containing SQL statements—upon specific events defined by the user, such as a row insertion or an update.

Triggers operate closely with DDL (Data Definition Language) and DML (Data Manipulation Language) operations, each serving distinct roles in managing database structures and data manipulation, respectively. Understanding the difference between DDL and DML is crucial for effective database management, as it lays the groundwork for employing triggers and stored procedures to automate and enhance database functionality.

Syntax

A trigger can be created by using the following syntax.

Let’s discuss the different parts of the syntax:

• CREATE [OR REPLACE] TRIGGER trigger_name: Name your trigger.
• {BEFORE / AFTER / INSTEAD OF}: Choose when the trigger acts (before, after, or instead of a DML event).
• {INSERT [OR] / UPDATE [OR] / DELETE}: Specify the DML operation (insert, update, delete) that activates the trigger.
• ON table_name: Attach the trigger to a table.
• [FOR EACH ROW]: Opt for a row-level trigger; without this, it defaults to a statement-level trigger.
• WHEN (condition): Set conditions under which the trigger should execute.
• [trigger_body]: Define the triggers' actions once activated.

When to use Triggers in SQL?

Here are some common situations where employing triggers can be beneficial:

• Automating data validation before or after changes to ensure data integrity.
• Synchronizing data across tables to maintain consistency across your database.
• Enforcing business rules and constraints that cannot be defined through standard data definition language (DDL) constraints.
• Capturing audit trails by logging changes to data, such as inserts, updates, and deletes.
• Implementing complex business logic that requires actions to be taken automatically in response to data modifications.
• Cascading changes to maintain referential integrity beyond simple foreign key constraints.
• Filling or updating denormalized tables to optimize query performance.

Types of Triggers

The following are the different types of triggers in SQL.

DDL Triggers

These triggers fire in response to data definition language (DDL) statements like CREATE, ALTER, or DROP.

Syntax:

Code:

Output:

Explanation: DDL triggers fire in response to data definition language (DDL) events like creating, altering, or dropping tables. They're useful for enforcing database schema constraints and auditing schema changes.

DML Triggers

These triggers fire in response to data manipulation language (DML) statements like INSERT, UPDATE, or DELETE.

Syntax:

Code & Output:

• This DML trigger named update_salary_trigger fires after an update operation on the employees table.
• It checks if the updated salary (:NEW.salary) is greater than the previous salary (:OLD.salary) for each row, inserting a record into salary_changes if true.

After Triggers

These triggers execute after the database has processed a specified event (such as an INSERT, UPDATE, or DELETE statement). AFTER triggers are commonly used to perform additional processing or auditing tasks after a data modification.

Syntax:

Code:

Output:

• The trigger fires when a new record is inserted into the employees table.
• It then inserts a corresponding entry into the audit_log table, indicating the insertion of a new employee.

Explanation: After triggers execute after an event, such as an insert, occurs on the table. They help log changes or maintain data integrity after the modification.

Instead Triggers

These triggers are used for views and fire instead of the DML statement (INSERT, UPDATE, DELETE) on the view.

Syntax:

Code:

Output: This syntax defines an "instead of" trigger named instead_trigger on the employees table, to be executed instead of an insert operation for each row.

Explanation: This trigger intercepts the action and executes the defined trigger logic instead of executing the standard insert operation, providing an alternative behavior for inserting data into the specified table.

LOGON Triggers

These triggers fire when a user logs into the database.

Syntax:

Example:

Output: Upon successful user logon, the message "User logged in successfully." is displayed.

Explanation: LOGON sql triggers are fired after a user successfully logs on to the database, allowing for actions or checks to be performed automatically upon login.

How to SHOW Triggers in SQL Server?

To display existing triggers in SQL Server, you can use the system catalog views. Below is the syntax, code, and output along with a brief explanation:

Syntax:

Code:

Output:

Explanation: This query retrieves all triggers defined in the current database from the system catalog view sys.triggers, allowing you to inspect the triggers present in the database.

How to UPDATE Triggers in SQL Server?

Updating triggers in SQL Server allow for executing custom actions when a record is updated in a table. They are useful for enforcing complex business rules or performing additional tasks alongside the update operation.

Syntax:

Code:

Output: When an update operation is performed on the Employees table, this trigger inserts a record into the EmployeeLog table, capturing the EmployeeID and the current date.

Explanation: This sql triggers is executed after an update operation on the Employees table (FOR UPDATE). It logs the updates by inserting a new record into the EmployeeLog table, capturing the EmployeeID and the current date using the GETDATE() function.

How to DELETE Triggers in SQL Server?

Triggers in SQL Server can be deleted using the DROP TRIGGER statement. This allows you to remove triggers that are no longer needed or to update existing triggers.

Syntax:

• [schema_name . ]: Optional schema qualifier for the trigger.
• trigger_name: Name of the trigger to be deleted.

Code:

Output: The trigger named MyTrigger in the dbo schema will be deleted from the database.

Explanation: The DROP TRIGGER statement removes the specified trigger from the database schema. This is useful for managing triggers, such as removing obsolete SQL triggers or changing trigger logic.

Advantages of Triggers

• Triggers in SQL can replace schedulers by immediately checking data changes, eliminating the need for background scheduler applications.
• They enhance security by enforcing additional checks on sensitive data during insertions, updates, or deletions, ensuring system integrity.
• Triggers prevent the insertion of invalid data into columns by validating input or performing corrective actions, such as logging errors or data manipulation.
• They facilitate cleanup operations by automatically deleting related data in other tables when specific events, like deletions, occur in the main table, minimizing errors and ensuring data consistency.

Disadvantages of Triggers in SQL

• Triggers can be challenging to troubleshoot due to their automatic execution, leading to potential unknown updates that are time-consuming to debug, especially in large projects.
• They increase database overhead by performing additional operations for each row affected by DML queries, such as updates, insertions into new tables, or unnecessary deletions.
• Triggers offer limited validation capabilities, primarily for checks like Not Null, Equality, and Unique constraints. However, they may only provide comprehensive solutions for some validation needs, requiring additional validation outside SQL, which can be costlier.