Defining Triggers for Insert, Update, Delete Operations
In SQL database management systems, triggers are special types of stored procedures or table view-based tasks in database tables that automatically execute or “run” when a database user applies a specific event to a table or view. View triggers in database tables can be manually applied for table operations such as INSERT, UPDATE, or DELETE. These system-defined triggers help automate tasks such as enforcing business rules in tables, validating table view data, or maintaining the consistency of database table data.
Trigger Overview in SQL Database Management Systems.
- Insert Trigger – This event automatically fires or runs when a new record is inserted or added to the table by the database user.
- Update Trigger – This event triggers or fires when an existing table view record is updated in the database table.
- Delete Trigger – This event triggers or fires when an existing or current table record is deleted from the table in the database table. Here, the delete trigger can be manually defined to execute before or after a task operation.
- BEFORE Trigger – This executes before the database table operation such as INSERT, UPDATE, DELETE, or other tasks.
- AFTER Trigger – This automatically executes after the database table operation is completed.
Use triggers in database tables to perform action tasks, such as INSERT, UPDATE, or DELETE triggers, which can be set for a task, and these can execute at the row level for each table row or at the statement level, executing once for the entire statement.
Defining triggers for INSERT, UPDATE, and DELETE operations in database tables.
Example of MySQL database table triggers.
MySQL Insert trigger in database management.
An insert trigger in the employee database table automatically executes and inserts a record into an audit table whenever a new employee record is added or inserted.
CREATE TRIGGER AfterNewEmployeeInsertTask
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO audit_log (action, employee_id, action_time)
VALUES (‘INSERT’, NEW.employee_id, NOW());
END;
This AFTER INSERT trigger in the employee database table executes automatically after a new employee is inserted into the table.
Here, NEW.employee_id displays the value of the employee_id column from the newly inserted table row.
MySQL Update Trigger in database management.
The update trigger in the employee database table records any modifications made to an employee’s salary in an audit log.
CREATE TRIGGER BeforeEmployeeSalaryUpdate
BEFORE UPDATE ON employee
FOR EACH ROW
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_time)
VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
END IF;
END;
Here, the BEFORE UPDATE trigger in the employee table executes automatically before the employee salary record is updated.
Here, OLD.salary displays the employee’s salary value before the update, and NEW.salary displays the new salary value being added.
MySQL Delete Trigger in database management.
Here, the delete trigger in the employee table logs and displays the employee record after it is deleted in an audit table.
CREATE TRIGGER AfterEmployeeDeleteTask
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 fires when an existing employee record is deleted from the employee table.
Here, OLD.employee_id indicates the employee ID of the deleted table row.
Example of a PostgreSQL INSERT, UPDATE, or DELETE event trigger in a database management system.
PostgreSQL Insert trigger in database management.
In a PostgreSQL database management system, database users can create an insert trigger in a similar way using the AFTER INSERT trigger event.
CREATE OR REPLACE FUNCTION log_employee_insert()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (action, employee_id, action_time)
VALUES (‘INSERT’, NEW.employee_id, NOW());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER AfterEmployeeInsertTask
AFTER INSERT ON employee
FOR EACH ROW
EXECUTE FUNCTION log_employee_insert();
Here, the AFTER INSERT trigger is defined to call the log_employee_insert() function after each insert into the employee table.
Here, NEW indicates the new row being inserted into the table.
PostgreSQL Update Triggers in database management.
Here is an example of a simple update trigger in the PostgreSQL database management system to log user modifications to the salary.
CREATE OR REPLACE FUNCTION log_salary_update()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.salary <> NEW.salary THEN
INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_time)
VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER BeforeEmployeeUpdateTask
BEFORE UPDATE ON employee
FOR EACH ROW
EXECUTE FUNCTION log_salary_update();
Here, the BEFORE UPDATE event trigger on the employee table analyses whether the salary in the employee table has been modified, and if the salary has been modified, it logs and displays the old and new salary values in the result.
PostgreSQL Delete Triggers in database management.
In the PostgreSQL database management system, a delete trigger logs in the system when records are deleted from the employee table.
CREATE OR REPLACE FUNCTION log_employee_delete()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO employee_deletion_log (employee_id, deletion_time)
VALUES (OLD.employee_id, NOW());
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER AfterEmployeeDeleteTask
AFTER DELETE ON employee
FOR EACH ROW
EXECUTE FUNCTION log_employee_delete();
Here, the AFTER DELETE event trigger on the employee table logs and displays the employee_id of the deleted employee record in the employee_deletion_log table.
Examples of INSERT, UPDATE, or DELETE in SQL Server.
Insert Trigger in SQL Server database management system.
In the SQL Server database management system, you can create and manage triggers for insert trigger actions.
CREATE TRIGGER AfterEmployeeInsertTask
ON employee
AFTER INSERT
AS
BEGIN
INSERT INTO audit_log (action, employee_id, action_time)
SELECT ‘INSERT’, employee_id, GETDATE()
FROM inserted;
END;
Here, the AFTER INSERT trigger on the employee table runs after a new row is inserted into the employee table.
`inserted` is a special table in SQL Server that holds the newly inserted employee table rows.
Update Trigger in SQL Server database management system.
Here, the SQL Server trigger on the employee table logs and displays any modifications made to an employee’s salary.
CREATE TRIGGER BeforeEmployeeUpdateTask
ON employee
AFTER UPDATE
AS
BEGIN
DECLARE @emp_id INT, @old_salary DECIMAL, @new_salary DECIMAL;
SELECT @emp_id = employee_id, @old_salary = salary FROM deleted;
SELECT @new_salary = salary FROM inserted;
IF @old_salary <> @new_salary
BEGIN
INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_time)
VALUES (@emp_id, @old_salary, @new_salary, GETDATE());
END
END;
Here, the AFTER UPDATE event trigger on the employee table compares the old and new salary values. If there is any modification, it logs and displays the update to the employee table in the salary_audit table.
Delete Trigger in SQL Server database management system.
The delete trigger in the SQL Server employee table is used to log and display table records after they are deleted.
CREATE TRIGGER AfterEmployeeDeleteTask
ON employee
AFTER DELETE
AS
BEGIN
INSERT INTO employee_deletion_log (employee_id, deletion_time)
SELECT employee_id, GETDATE() FROM deleted;
END;
Here, the AFTER DELETE event trigger on the Employee table logs the deleted employee table record in the employee_id column, which is stored in a special Deleted table.
Key Concepts in INSERT, UPDATE, or DELETE Triggers.
- Inserted and Deleted Tables – Most RDBMS table event triggers have special database tables named Inserted and Deleted.
- Inserted – This stores the new database table values for INSERT and UPDATE table operations.
- Deleted – This stores the old deleted table values for DELETE and UPDATE database table operations.
- Row-Level vs. Statement-Level event triggers.
- Row-Level Triggers – These run once for each table row affected by a task or operation in the database table, such as when a new table row is inserted.
- Statement-Level Triggers – These fire or run once for the entire database table operation, regardless of the number of table rows impacted.
Trigger Events: Considerations and Best Practices in Multiple Use.
- Performance Impact – Event triggers created in database tables can impact performance. This is especially true when row-level triggers fire or run for every affected row in the database table. Here, you can mitigate their impact on performance by developing or designing event triggers in the proper order.
- Recursive Triggers – Handle recursive triggers in database tables carefully, where one trigger can fire or run another trigger, potentially leading to infinite loops. Some database software allows the database user to stop this.
- Error Handling – Always apply error handling methods within event triggers to avoid unwanted failures or restrictions in database table operations.
Conclusion of Defining Triggers for Insert, Update, and Delete Operations.
Triggers are a popular and powerful feature in SQL database management systems for editing, controlling, or modifying data. They allow database users to perform database tabular actions automatically based on event triggers such as INSERT, UPDATE, or DELETE. Event triggers are commonly used for database table auditing, applying business rules, and maintaining data consistency. By using event triggers effectively in database tables, database users can automate their tasks.
