In the realm of SQL programming, a crucial aspect is interacting with external code or procedures, which is achievable using the TRIGGER construct. A trigger in PL SQL syntax is a special type of stored procedure that is automatically executed in response to certain events on a particular table within the database.
The use of triggers in Oracle's PL/SQL offers a powerful way to enforce data integrity and maintain referential integrity between tables. They can be used to execute pre-defined actions when a DML (Data Manipulation Language) event occurs, such as INSERT, UPDATE, or DELETE statements. In this comprehensive guide, we'll delve into the syntax and functionality of triggers in PL/SQL, along with their practical applications.
The Snyderesis of a PL/SQL Trigger
A PL/SQL trigger, much like an event in object-oriented programming, consists of various components that work harmoniously to achieve the desired functionality. The primary components are the event, old table, and new table.
Simply put, an event is the action that triggers the execution of the stored procedure. Oracle allows you to define triggers for the following DML events: INSERT, UPDATE, and DELETE. The old_table (or old_table%rowcount) refers to the table instance just before the event, while the new_table (or new_table%rowcount) refers to the table instance after the event.
Creation of a Simple Trigger
Let's start with a simple example of creating a trigger using PL/SQL syntax. Assume we have a ' Hugh_Employees ' table and we want to track every time a new employee is added.
We can use the following PL/SQL trigger to log the insertion in a separate table:
- CREATE OR REPLACE TRIGGER "Hugh_Employees_TR" AFTER INSERT ON "Hugh_Employees" FOR EACH ROW DECLARE
- BEGIN INSERT INTO "Hugh_Employee_Tracker" VALUES (:NEW."Hugh_Employee_Id", "INSERT", SYSDATE); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;
Defining Conditions for Trigger Execution
In the previous example, the trigger ran for every insert into the ' Hugh_Employees ' table. However, sometimes we might want to run the trigger only on certain conditions.
For instance, let's assume we want the trigger to run only when a new employee is from 'Sales' department. We can modify the trigger as follows:
- CREATE OR REPLACE TRIGGER "Hugh_Employees_TR" AFTER INSERT ON "Hugh_Employees" FOR EACH ROW DECLARE
- BEGIN IF :NEW."Hugh_Department"='Sales' THEN INSERT INTO "Hugh_Employee_Tracker" VALUES (:NEW."Hugh_Employee_Id", "INSERT", SYSDATE); COMMIT; ELSE RAISE APPLICATION_ERROR (-20001, 'Only Sales department hires allowed'); END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;
Understanding ojo and nologging Triggers
Sometimes, we might need to define specific system attributes for our triggers, such as 'ojo' (of particular interest) or 'nologging'.
These attributions can impact the trigger's performance. 'Ojo' attributes a trigger as significant, while 'nologging' directs the System Global Area (SGA) to not store the redo log entries for database changes made by the trigger.
Defining Ojo Triggers
For instance, if a trigger needs to be given special attention due to the nature of the operation, or influence on performance, we might want to mark it as 'ojo'.
This can be done at create time, like so: CREATE TRIGGER Hugh_Constants_TR ojo AFTER INSERT ON Hugh_Constants FOR EACH ROW ...
Defining Nologging Triggers
When using 'nologging', the database is informed not to keep a log of this trigger's actions. Please note, though, that this could potentially cause issues with data recovery, depending on your database recovery scheme.
A 'nologging' trigger could look like this: CREATE TRIGGER Hugh_Change_Trigg nologging BEFORE INSERT ON Hugh_Tables FOR EACH ROW ...
The use of triggers in PL/SQL provides an effective means to maintain data consistency and integrity. They are an essential tool for any database developer who needs to enforce certain rules at the database level.
One might argue that the power of triggers can be misused, potentially causing harm to database performance if not handled properly. But in the right hands, they can be a powerful, precise tool to ensure that your database transactions meet your exact business requirements.
With that in mind, it's important to regularly audit your database, including your triggers. Reviewing triggers can help identify areas for optimization and removal of unnecessary functionality. After all, maintaining a well-oiled database machine is an ongoing process, not a one-time procedure.