SQL-Trigger

What are Triggers?

In a database, triggers are special types of stored procedures that are automatically executed or fired when specific database events occur. These events could be actions like INSERT, UPDATE, or DELETE on a table or view. Triggers are used to enforce business rules, maintain audit trails, or automate system tasks.

Advantages of Triggers

1. 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.
2. Audit Trail - Triggers can track changes in a database, providing an audit trail of INSERT, UPDATE, and DELETE operations.
3. Performance - By automating repetitive tasks, triggers improve SQL query performance and reduce manual workload.

Types of Triggers

Triggers can be categorized based on their use cases and the database system. Below are the most common types.

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

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.