Triggers in PL/SQL are a special type of stored procedure that are automatically executed, or “triggered,” by the database in response to specific events. These events can include INSERT, UPDATE, or DELETE statements on a specific table or view, or a specific combination of events such as an INSERT followed by an UPDATE.
For example, consider a company’s “employees” table. When a new employee is added to the table (an INSERT statement is executed), the company may want to automatically update the “departments” table to reflect the new employee’s department. This can be achieved by creating a trigger that is activated by an INSERT statement on the “employees” table and that performs the necessary update on the “departments” table.
Here is an example of how to create a trigger in PL/SQL that updates the “departments” table when a new employee is added to the “employees” table:
In this example, the trigger is named “update_department” and it is activated “AFTER” an INSERT statement on the “employees” table. The “FOR EACH ROW” clause means that the trigger will execute once for each row affected by the INSERT statement. The code inside the BEGIN-END block performs the necessary update on the “departments” table, using the “:new” pseudo-record to access the values of the new employee’s department_id.
It’s important to be aware that triggers can have a significant impact on the performance of your database, so it’s important to use them judiciously and to test their performance under heavy loads.
Triggers can also be created to fire before the event, known as a “BEFORE” trigger, rather than after the event as in the previous example. For example, you might want to validate the data before it is inserted into a table and prevent the insertion if the data is not valid.
This trigger, named “validate_employee“, is activated “BEFORE” an INSERT statement on the “employees” table, and it checks the value of the new employee’s salary. If the salary is less than 10000, the trigger raises an application error, which causes the INSERT statement to be rolled back and the error message to be displayed to the user.
Triggers can also be created for UPDATE and DELETE statements in a similar way. For example, you might want to keep track of changes to a table by logging the old and new values of the row in an audit table when an update or delete is made.
This trigger, named “log_changes“, is activated “AFTER” an UPDATE or DELETE statement on the “employees” table. The trigger uses the “UPDATING” and “DELETING” clauses to determine whether the statement is an update or a delete, and it inserts the appropriate information into the audit_log table.
It’s worth noting that triggers can also call other stored procedures, functions, and packages and also can call other triggers, this can create a cascade effect and can be dangerous if not well designed, so it’s important to be aware of the dependencies and cascading triggers when working with triggers.
