SQL Triggers

SQL triggers are essential in database management systems (DBMS). They enable SQL statements to run when specific database events occur such as when someone adds, changes, or removes data. Triggers are commonly used to maintain data integrity, track changes, and apply business rules automatically, without needing manual input.

What is SQL Triggers?

A trigger is a stored procedure in adatabase 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.

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.

Why Should You Use SQL Triggers?

Using SQL triggers brings several benefits to the table:

• Automation: Triggers handle repetitive tasks, saving our time and effort.
• Consistency & Data Integrity: Automatically enforcing rules ensures that our data remains clean and accurate.
• Business Rules Enforcement: Triggers can help ensure that changes to our database follow your business logic.
• Audit Trails: Track changes automatically, making it easier to monitor and record data updates.

Now that you know why triggers are important, let’s look at how to create and use them effectively.

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: Prevent Table Deletions

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

DML triggers fire when we manipulate data with commands like INSERT, UPDATE, or DELETE. These triggers are perfect for scenarios where we need to validate data before it is inserted, log changes to a table, or cascade updates across related tables.

Example: Prevent Unauthorized Updates Let’s say you want to prevent users from updating the data in a sensitive students table. We can set up a trigger to handle that:

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

Output

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: Track User Logins

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

Real-World Use Cases of SQL Triggers

1. Automatically Updating Related Tables (DML Trigger Example)

Triggers can automatically perform tasks, like updating related tables when data changes. Imagine we have a database for students, where the student_gradestable holds individual subject grades. If the grade of a student is updated, we may also need to update the total_scorestable

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;

This ensures that every time a student's grade is updated, the total score in the total_scores table is automatically recalculated.

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. For instance, we may want to ensure that the grades being inserted are within a valid range (say 0 to 100).

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;

The trigger checks if the inserted grade is valid. If not, it throws an error and prevents the insertion.

Viewing and Managing 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.

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: These run before the action (INSERT, UPDATE, DELETE) is executed. They’re great for data validation or modifying values before they are committed to the database.
  
• 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;

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.

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.