Oracle PL/SQL Statement Level Triggers are a type of database trigger in Oracle Database that fires once for each SQL statement, regardless of the number of rows affected. These triggers are useful for enforcing business rules or executing specific actions before or after a particular type of SQL statement is executed.
Here’s a breakdown of key aspects related to Oracle PL/SQL Statement Level Triggers:
Trigger Syntax
The syntax for creating a PL/SQL Statement Level Trigger is as follows:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | ALL}
ON table_name
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
WHEN (condition)
DECLARE
-- Declaration section for variables and constants
BEGIN
-- Trigger logic
END;
BEFORE/AFTER/INSTEAD OF: Specifies whether the trigger should fire before, after, or instead of the triggering event.
INSERT/UPDATE/DELETE/ALL: Specifies the type of SQL statement that will activate the trigger.
ON table_name: Specifies the table associated with the trigger.
REFERENCING OLD AS old NEW AS new: Used to reference old and new values in case of an UPDATE or DELETE statement.
FOR EACH ROW: Indicates that the trigger is a Row Level Trigger. Omitted for Statement Level Triggers.
Trigger Timing
Statement Level Triggers can be defined to execute either before or after the triggering event.
The BEFORE keyword indicates that the trigger should fire before the triggering event.
The AFTER keyword indicates that the trigger should fire after the triggering event.
Use Cases
Data Validation: Statement Level Triggers can be used to enforce data integrity by validating the data before it is inserted, updated, or deleted.
Audit Trails: They are often employed to maintain audit trails by recording information about the executed statements for tracking purposes.
Security: Statement Level Triggers can be used to implement security measures, such as restricting access or modifying data based on certain conditions.
Example
CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE INSERT
ON employees
DECLARE
-- Declaration section
BEGIN
-- Trigger logic (e.g., data validation)
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
END;
/
In this example, the trigger before_insert_trigger fires before an INSERT statement on the employees table and checks if the salary being inserted is non-negative.
Care should be taken while implementing triggers, as poorly designed triggers can impact performance and lead to unexpected behavior.
In summary, Oracle PL/SQL Statement Level Triggers provide a powerful mechanism for executing custom logic in response to SQL statements. They are versatile and can be employed for various purposes, including data validation, audit trails, and security enforcement. However, it's essential to use them judiciously and consider the potential impact on performance.
