Blog

Trigger Timing (BEFORE, AFTER) in sql

In SQL database management systems, database table query event triggers can be defined, created, or manually set with multiple timings, where BEFORE or AFTER query event triggers help users create multiple timings, specifying when the trigger should fire or run in relation to DML (Data Manipulation Language) database operations such as INSERT table query operations, UPDATE tasks, or DELETE data associated with user-generated tasks. The BEFORE or AFTER database table query event or action can be manually defined and triggered.

BEFORE Triggers in SQL Database Management Systems.

Remember that database user-created BEFORE event triggers fire before the actual DML operation is executed or run on the database table query. This means that the event trigger executes first in the database query; if the event trigger executes successfully, then the data manipulation operation-related tasks proceed to the next step.

BEFORE Trigger Use Case – BEFORE triggers in database tables are mostly used to validate table data, manually modify table data before insertion or update, or to stop the operation if certain user-defined conditions are not met in the database table.

Example of a BEFORE INSERT event trigger.

Here, let’s assume we want to ensure in the employee table that a database user cannot set an employee’s salary to a negative value before inserting a new record.

CREATE TRIGGER BeforeInsertEmployeeEvent

BEFORE INSERT ON employee

FOR EACH ROW

BEGIN

IF NEW.salary < 0 THEN

SIGNAL SQLSTATE ‘55000’ SET MESSAGE_TEXT = ‘You cannot Set Salary as negative’;

END IF;

END;

In this example, the database user-created event trigger checks or analyzes the value of the salary column before a new record insertion. If the employee salary is negative, the user-created event trigger displays an error and stops the user before inserting a new record.

Example of a BEFORE UPDATE event trigger.

If a database user tries to manually update an employee’s salary to an undesirable value in the employee table, the database user can apply a BEFORE UPDATE event trigger to restrict this process.

CREATE TRIGGER BeforeUpdateEmployeeTask

BEFORE UPDATE ON employee

FOR EACH ROW

BEGIN

IF NEW.salary < OLD.salary THEN

SIGNAL SQLSTATE ‘55000’ SET MESSAGE_TEXT = ‘You cannot update Salary’;

END IF;

END;

In this example, the BEFORE UPDATE event trigger checks whether the updated salary in the database table is less than the old salary. If it is, it displays an error to the database user to stop the salary update task.

AFTER triggers in SQL Database Management Systems.

A user-created AFTER event trigger in SQL database tables fires or runs after the actual DML operation is successfully completed. This means that the user-created event trigger executes only after the operation applied to the user-defined database table query is complete. The AFTER trigger event operation is an ideal choice for maintaining user logging in the database, system-generated audit trail tasks, or similar event actions that should occur after the user-defined task or operation is complete.

AFTER event trigger use case – AFTER event triggers in database tables are commonly used to perform user-generated database tasks or actions such as logging modifications after data manipulation, updating other related database tables, or notifying other table systems.

Example of an AFTER INSERT event trigger.

Here, let’s assume that after a database user inserts a new employee record into the employee table, the database user wants to log this event in an audit table.

CREATE TRIGGER AfterInsertEmployeeTask

AFTER INSERT ON employee

FOR EACH ROW

BEGIN

INSERT INTO audit_log (action, employee_id, action_time)

VALUES (‘INSERT’, NEW.employee_id, NOW());

END;

In this example, the AFTER INSERT event trigger fires or runs after a new record insertion operation, and logs the system action in the audit_log table.

Example of an AFTER UPDATE event trigger.

Here, let’s assume that the database user wants all changes to an employee’s position or salary in the employee table to be updated in a separate table, employee_modification.

CREATE TRIGGER AfterUpdateEmployeeTask

AFTER UPDATE ON employee

FOR EACH ROW

BEGIN

INSERT INTO employee_modification (employee_id, old_position, new_position, old_salary, new_salary, change_time)

VALUES (NEW.employee_id, OLD.position, NEW.position, OLD.salary, NEW.salary, NOW());

END;

This AFTER event trigger logs and displays the old and new values ​​of the employee’s position and salary after an employee record is updated.

Example of an AFTER DELETE event trigger.

If a record of an existing employee is deleted from the employee table, the database user can store a log of the employee deletion here.

CREATE TRIGGER AfterDeleteEmployeeTask

AFTER DELETE ON employee

FOR EACH ROW

BEGIN

INSERT INTO employee_deletion_log (employee_id, deletion_time)

VALUES (OLD.employee_id, NOW());

END;

Here, the AFTER DELETE event trigger in the employee table logs and displays the deletion process of an employee record, where the employee_id and the timestamp of the deleted record are stored.

Main Differences Between BEFORE and AFTER evet Triggers

Conclusion on Trigger Timing (BEFORE, AFTER) in SQL.

• BEFORE event triggers are best for data validation and modification before any type of database table operation, allowing database users to block false table query operations or apply any industry or business rules to the database table.
• AFTER event triggers in a database table are useful for tasks that should occur after the database table query operation is complete. For example, database or user logging, auditing system task activities, and synchronizing table data in related database tables.
• The selection between BEFORE and AFTER event triggers in a database table operation depends on whether the database user needs to stop an event or action, or perform an additional task after the database operation.