Trigger - (PL/SQL Trigger)

A trigger in PL/SQL is a special kind of stored procedure that is automatically executed in response to certain events. These events can be data manipulation events, such as INSERT, UPDATE, or DELETE statements, or other events, such as database or schema-level events.

Syntax

The syntax for creating a trigger in PL/SQL is as follows:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE } ON table_name
[FOR EACH ROW]
DECLARE
   -- Variable declarations go here
BEGIN
   -- Trigger logic goes here
END;

Here, trigger_name is the name of the trigger, which can be preceded by the OR REPLACE clause. The trigger can be defined to execute BEFORE or AFTER the DML statement (INSERT, UPDATE, or DELETE) on the specified table_name, and can be specified to execute FOR EACH ROW. The DECLARE block is used to declare any necessary variables, while the BEGIN and END blocks contain the trigger logic.

Example

Here is an example of a trigger that automatically updates the last_update column whenever a row is updated in the employees table:

CREATE OR REPLACE TRIGGER update_employee_last_update
AFTER UPDATE ON employees
FOR EACH ROW
DECLARE
BEGIN
   UPDATE employees SET last_update = SYSDATE
   WHERE employee_id = :OLD.employee_id;
END;

In this example, the trigger is named update_employee_last_update and is defined to execute AFTER an UPDATE statement on the employees table. The FOR EACH ROW clause specifies that the trigger should execute once for each row that is affected by the UPDATE statement. The DECLARE section is empty in this example, and the BEGIN and END blocks contain a single SQL statement that updates the last_update column for the row that was updated.

Output

When an UPDATE statement is executed on the employees table in the above example, the update_employee_last_update trigger is automatically executed after the statement completes. The trigger updates the last_update column for the affected row, and the updated row is returned as output.

Explanation

In the above example, the trigger executes the UPDATE statement that sets the last_update column for the affected row to the current date and time. The WHERE clause of the UPDATE statement uses the :OLD keyword to access the value of the employee_id column in the row that was updated by the UPDATE statement. This ensures that only the row that was updated by the statement is modified by the trigger.

Use

Triggers can be used in PL/SQL to automate database tasks and enforce data integrity constraints. They are often used to implement auditing and logging functionality, populate audit trails or log tables, set default values for columns, or enforce complex business rules.

Important Points

• A trigger in PL/SQL is a type of stored procedure that is automatically executed in response to certain events.
• Triggers can be defined using the CREATE TRIGGER statement, which specifies the name of the trigger, the event that triggers the trigger, and the relevant table.
• Triggers can be used to implement auditing and logging functionality, populate audit trails or log tables, set default values for columns, or enforce complex business rules.

Summary

In summary, a trigger in PL/SQL is a special kind of stored procedure that is automatically executed in response to certain events. Triggers can be used to automate database tasks and enforce data integrity constraints, such as auditing and logging functionality, populating audit trails or log tables, setting default values for columns, and enforcing complex business rules. Triggers are a powerful tool in PL/SQL programming and are very useful in maintaining the integrity of a database.