SQL Trigger

SQL triggers are a critical feature in database management systems (DBMS) that provide automatic execution of a set of SQL statements when specific database events, such as INSERT, UPDATE, or DELETE operations, occur. Triggers are commonly used to maintain data integrity, track changes, and enforce business rules automatically, without needing manual input.

In this article, we will explain SQL triggers, explaining their types, syntax, and practical use cases. We will explain different types of triggers, such as DML triggers, DDL triggers, and logon triggers, and discuss how SQL triggers work in various database systems like SQL Server, MySQL, and Oracle

What is SQL Trigger?

A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. By using SQL triggers, developers can automate tasks, ensure data consistency, and keep accurate records of database activities. For example, a trigger can be invoked when a row is inserted into a specified table or when specific table columns are updated.

In simple words, a trigger is a collection of SQL statements with particular names that are stored in system memory. It belongs to a specific class of stored procedures that are automatically invoked in response to database server events. Every trigger has a table attached to it.

Key Features of SQL Triggers:

• Automatic Execution: Triggers fire automatically when the defined event occurs (e.g., INSERT, UPDATE, DELETE).
• Event-Driven: Triggers are tied to specific events that take place within the database.
• Table Association: A trigger is linked to a specific table or view, and operates whenever changes are made to the table’s data.

Syntax

create trigger [trigger_name] 
[before | after]  
{insert | update | delete}  
on [table_name]  
FOR EACH ROW
BEGIN
END;

Key Terms

• trigger_name: The name of the trigger to be created.
• BEFORE | AFTER: Specifies whether the trigger is fired before or after the triggering event (INSERT, UPDATE, DELETE).
• {INSERT | UPDATE | DELETE}: Specifies the operation that will activate the trigger.
• table_name: The name of the table the trigger is associated with.
• FOR EACH ROW: Indicates that the trigger is row-level, meaning it executes once for each affected row.
• trigger_body: The SQL statements to be executed when the trigger is fired.

Types of SQL Triggers

Triggers can be categorized into different types based on the action they are associated with:

1. DDL Triggers 

The Data Definition Language (DDL) command events such as Create_table, Create_view, drop_table, Drop_view, and Alter_table cause the DDL triggers to be activated. They allow us to track changes in the structure of the database. The trigger will prevent any table creation, alteration, or deletion in the database.

Example:

CREATE TRIGGER prevent_table_creation
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS 
BEGIN
   PRINT 'you can not create, drop and alter table in this database';
   ROLLBACK;
END;

Output

2. DML Triggers

The Data manipulation Language (DML) command events that begin with Insert, Update, and Delete set off the DML triggers. DML triggers are used for data validation, ensuring that modifications to a table are done under controlled conditions.

Example:

CREATE TRIGGER prevent_update 
ON students
FOR UPDATE 
AS 
BEGIN 
   PRINT 'You can not insert, update and delete this table i'; 
   ROLLBACK; 
END;

Output

DML Trigger

3. Logon Triggers

These triggers are fired in response to logon events. Logon triggers are useful for monitoring user sessions or restricting user access to the database. As a result, the PRINT statement messages and any errors generated by the trigger will all be visible in the SQL Server error log. Authentication errors prevent logon triggers from being used. These triggers can be used to track login activity or set a limit on the number of sessions that a given login can have in order to audit and manage server sessions.

Example:

CREATE TRIGGER track_logon
ON LOGON
AS
BEGIN
   PRINT 'A new user has logged in.';
END;

Using SQL Triggers: Practical Use Cases

1. Automatically Updating Related Tables (DML Trigger Example)

Triggers can automatically perform tasks, like updating related tables when data changes. For instance, when a student’s grade is updated, the total score in the related table can also be updated automatically.

Example:

CREATE TRIGGER update_student_score
AFTER UPDATE ON student_grades
FOR EACH ROW
BEGIN
   UPDATE total_scores
   SET score = score + :new.grade
   WHERE student_id = :new.student_id;
END;

2. Data Validation (Before Insert Trigger Example)

Triggers can be used to validate data before it is inserted into a table, ensuring that the data follows specific business rules.

Example:

CREATE TRIGGER validate_grade
BEFORE INSERT ON student_grades
FOR EACH ROW
BEGIN
   IF :new.grade < 0 OR :new.grade > 100 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Invalid grade value.');
   END IF;
END;

Output

This trigger ensures that no grade less than 0 or greater than 100 is inserted into the student_grades table.

Viewing Triggers in SQL

If we are working with many tables across multiple databases, we can use a simple query to list all available triggers in our SQL Server instance. This is helpful for tracking and managing triggers, especially when dealing with tables that have similar names across databases.

Syntax

SELECT name, is_instead_of_trigger
FROM sys.triggers
WHERE type = ‘TR’;

Key Terms

• name: The name of the trigger.
• is_instead_of_trigger: Whether the trigger is an INSTEAD OF trigger.
• type = ‘TR’: This filters the results to show only triggers.

The SQL Server Management Studio makes it very simple to display or list all triggers that are available for any given table. The following steps will help us accomplish this:

Go to the Databases menu, select the desired database, and then expand it.

• Select the Tables menu and expand it.
• Select any specific table and expand it.
• We will get various options here. When we choose the Triggers option, it displays all the triggers available in this table.

BEFORE and AFTER Triggers

SQL triggers can be specified to run BEFORE or AFTER the triggering event.

• BEFORE Triggers: Execute before the actual SQL statement is executed. Useful for validating data before insertion or updating.
• AFTER Triggers: Execute after the SQL statement completes. Useful for logging or cascading updates to other tables.

Example: Using BEFORE Trigger for Calculations

Given Student Report Database, in which student marks assessment is recorded. In such a schema, create a trigger so that the total and percentage of specified marks are automatically inserted whenever a record is inserted. Here, a trigger will invoke before the record is inserted so BEFORE Tag can be used. 

Query

mysql>>desc Student;

Student Datatype

Below SQL statement will create a trigger in the student database in which whenever subjects marks are entered, before inserting this data into the database, the trigger will compute those two values and insert them with the entered values. In this way, triggers can be created and executed in the databases.

Stud_marks

Output

Output

Advantages of Triggers

• Data Integrity: Triggers help enforce consistency and business rules, ensuring that data follows the correct format.

• Automation: Triggers eliminate the need for manual intervention by automatically performing tasks such as updating, inserting, or deleting records when certain conditions are met.
• Audit Trail: Triggers can track changes in a database, providing an audit trail of INSERT, UPDATE, and DELETE operations.
• Performance: By automating repetitive tasks, triggers improve SQL query performance and reduce manual workload.

Conclusion

SQL triggers are a powerful feature for automating and enforcing rules in our database management system. Whether we’re ensuring data integrity, automating updates, or preventing unauthorized changes, triggers can save time and improve consistency in database operations. With various types of triggers available, including DML triggers, DDL triggers, and logon triggers, they can be tailored to a wide range of use cases. Understanding and implementing SQL triggers effectively is important for maintaining a strong and efficient database.

GeeksforGeeks

Improve

Article Tags :

• Databases
• DBMS
• SQL
• dbms
• DBMS-SQL
• SQL-basics

+2 More