An Oracle trigger is a database object that is used to automatically execute a set of actions in response to certain database events. These events can include changes to data in a table (such as inserts, updates, or deletes), database logon or logoff, or database startup or shutdown.
Triggers can be used to enforce business rules, audit database changes, generate summary data, or perform other automated tasks. Triggers are written in PL/SQL, which is Oracle's procedural programming language, and can be defined to execute either before or after the triggering event.
The basic syntax for creating a trigger in Oracle is:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} [OF column_name] ON table_name
[REFERENCING OLD AS old NEW AS new] [FOR EACH ROW]
DECLARE
-- local variables and declarations
BEGIN
-- PL/SQL statements to execute
EXCEPTION
-- exception handling code
END;
In this syntax, "trigger_name" is the name of the trigger, "BEFORE" or "AFTER" specifies when the trigger should fire, "INSERT", "UPDATE", or "DELETE" specifies the event that should trigger the trigger, and "table_name" is the name of the table that the trigger is associated with.
The "REFERENCING OLD AS old NEW AS new" clause allows the trigger to refer to the old and new values of the row being modified. The "FOR EACH ROW" clause specifies that the trigger should execute once for each row affected by the triggering event.
The PL/SQL statements to execute can include any valid PL/SQL code, including SQL statements. The exception handling code is used to handle any errors that may occur during the execution of the trigger.
Overall, Oracle triggers are a powerful feature of the database that can be used to automate many common tasks and enforce business rules.
Oracle Trigger Example
Let's say we have two tables, one called "orders" and another called "order_logs". Every time an order is inserted into the "orders" table, we want to log that event into the "order_logs" table.
First, we need to create the "order_logs" table:
CREATE TABLE order_logs (
log_id NUMBER,
order_id NUMBER,
log_date DATE
);
Next, we can create a trigger that will fire every time a new row is inserted into the "orders" table:
CREATE OR REPLACE TRIGGER log_order_inserts
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_logs (log_id, order_id, log_date)
VALUES (order_logs_seq.nextval, :new.order_id, sysdate);
END;
This trigger will insert a new row into the "order_logs" table with a unique ID generated by the "order_logs_seq" sequence, the ID of the order that was just inserted into the "orders" table, and the current date and time.
Note that the ":new" keyword refers to the newly inserted row in the "orders" table, so ":new.order_id" refers to the order ID of that row.
