Overview of Triggers
A database trigger is a compiled stored program unit, written in either PL/SQL or Java, that Oracle Database invokes ("fires") automatically in certain situations.
A trigger fires whenever one of the following operations occurs:
1. DML statements on a particular table or view, issued by any user DML statements modify data in schema objects. For example, inserting and deleting rows are DML operations.
2. DDL statements issued either by a particular user or any user DDL statements define schema objects. For example, creating a table and adding a column are DDL operations.
3. Database events User login or logoff, errors, and database startup or shutdown are events that can invoke triggers.
Triggers are schema objects that are similar to subprograms but differ in the way they are invoked. A subprogram is explicitly run by a user, application, or trigger. Triggers are implicitly invoked by the database when a triggering event occurs
Advantages of Triggers
The correct use of triggers enables you to build and deploy applications that are more robust and that use the database more effectively.
You can use triggers to:
• Automatically generate derived column values
• Prevent invalid transactions
• Provide auditing and event logging
• Record information about table access
You can use triggers to enforce low-level business rules common for all client applications. For example, several applications may access the employees table. If a trigger on this table ensures the format of inserted data, then this business logic does not need to be reproduced in every client. Because the trigger cannot be circumvented by the application, the business logic in the trigger is used automatically.
You can use both triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle strongly recommends that you only use triggers to enforce complex business rules not definable using an integrity constraint.
Excessive use of triggers can result in complex interdependencies that can be difficult to maintain in a large application. For example, when a trigger is invoked, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers that can produce unintended effects.
Types of Triggers
Triggers can be categorized according to their means of invocation and the type of actions they perform.
Oracle Database supports the following types of triggers:
• Row triggers
A row trigger fires each time the table is affected by the triggering statement. For example, if a statement updates multiple rows, then a row trigger fires once for each row affected by the UPDATE. If a triggering statement affects no rows, thena row trigger is not run. Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.
• Statement triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows affected by the triggering statement. For example, if a statement deletes 100 rows from a table, a statement-level DELETE trigger is fired only once. Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
• INSTEAD OF triggers
An INSTEAD OF trigger is fired by Oracle Database instead of executing the triggering statement. These triggers are useful for transparently modifying views that cannot be modified directly through DML statements.
• Event triggers
You can use triggers to publish information about database events to subscribers.
Event triggers are divided into the following categories:
– A system event trigger can be caused by events such as database instance startup and shutdown or error messages.
– A user event trigger is fired because of events related to user logon and logoff, DDL statements, and DML statements.
Timing for Triggers
You can define the trigger timing—whether the trigger action is to be run before or after the triggering statement.
A simple trigger is a single trigger on a table that enables you to specify actions for exactly one of the following timing points:
• Before the firing statement
• Before each row affected by the firing statement
• After each row affected by the firing statement
• After the firing statement
For statement and row triggers, a BEFORE trigger can enhance security and enable business rules before making changes to the database. The AFTER trigger is ideal for logging actions.
A compound trigger can fire at multiple timing points. Compound triggers help program an approach in which the actions that you implement for various timing points share common data.
Creation of Triggers
The CREATE TRIGGER statement creates or replaces a database trigger.
A PL/SQL trigger has the following general syntactic form:
CREATE TRIGGER trigger_name
triggering_statement
[trigger_restriction]
BEGIN
triggered_action;
END;
A PL/SQL trigger has the following basic components:
• Trigger name
The name must be unique among other trigger names in the same schema. For example, the name may be part_reorder_trigger.
• The trigger event or statement
A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to be invoked. For example, a user updates a table.
• Trigger restriction
A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. For example, the trigger is not invoked unless the number of available parts is less than a present reorder amount.
• Triggered action
A triggered action is the procedure that contains the SQL statements and code to be run when a triggering statement is issued and the trigger restriction evaluates to true. For example, a user inserts a row into a pending orders table.
Example: CREATE TRIGGER Statement
This example creates a trigger that fires when an INSERT, UPDATE, or DELETE statement executes on a line items table.
Suppose that you create the orders and lineitems tables with the following statements. The orders table contains a row for each unique order, whereas the lineitems table contains a row for each item in an order.
CREATE TABLE orders
( order_id NUMBER PRIMARY KEY,
/* other attributes */
line_items_count NUMBER DEFAULT 0 );
CREATE TABLE lineitems
( order_id REFERENCES orders,
seq_no NUMBER,
/* other attributes */
CONSTRAINT lineitems PRIMARY KEY(order_id,seq_no) );
The following statement creates a sample trigger that automatically updates the orders table with the number of items in an order:
CREATE OR REPLACE TRIGGER lineitems_trigger
AFTER INSERT OR UPDATE OR DELETE ON lineitems
FOR EACH ROW
BEGIN
IF (INSERTING OR UPDATING)
THEN
UPDATE orders SET line_items_count = NVL(line_items_count,0)+1
WHERE order_id = :new.order_id;
END IF;
IF (DELETING OR UPDATING)
THEN
UPDATE orders SET line_items_count = NVL(line_items_count,0)-1
WHERE order_id = :old.order_id;
END IF;
END
In lineitems_trigger, the triggering statement is an INSERT, UPDATE, or DELETE on the lineitems table. No triggering restriction exists. The trigger is invoked for each row changed.
The trigger has access to the old and new column values of the current row affected by the triggering statement.
Two correlation names exist for every column of the table being modified: the old value (:old), and the new value (:new). If a session updates or inserts rows in lineitems for an order, then after the action the trigger calculates the number of items in this order and updates the orders table with the count.
