TRIGGER

A trigger is a special class of stored procedure and is defined on tables or views. Triggers are executed automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or a View.

A trigger cannot be called or executed. Triggers are fired automatically by the database.

The database triggers are used for the following purpose:

1. To generate the resulting data automatically.
2. To enforce complex integrity constraints, security authorizations, and business rules.
3. To avoid invalid transactions.
4. To enforce referential integrity across nodes in a distributed database.
5. To provide transparent event logging and advanced auditing data modifications.
6. To maintain synchronous table replicates and check the status of the table access.

Triggers are categorized in the following three ways:

1. Row Level Triggers
2. Statement Level Triggers
3. Before and After Triggers

Row Level Trigger
The row level trigger specifies that a trigger will be fired each time a row in a table is affected by the trigger statement.

Statement Level Trigger
The statement level trigger specifies that a trigger will be fired on behalf of the trigger statement. The statement level trigger does not depend on the number of rows affected.

Before and After Triggers
While defining a trigger, you can specify whether to perform a trigger action (or execute trigger body) before or after the triggering statement.

Trigger Syntax
The syntax for creating a trigger is as follows:

CREATE OR REPLACE TRIGGER [schema.]trigger_name
{ BEFORE, AFTER }
{ DELETE, INSERT, UPDATE [ OF column, .... ] }
ON [schema.]table_name
[ REFERENCING {OLD [AS] old}
{NEW [AS] new} ]
[ FOR EACH ROW [ WHEN search_condition ]
DECLARE
-- Declarative section: variables, types, and local subprograms
BEGIN
-- Executable section: procedural and SQL statements
-- This is the only section of the block required
EXCEPTION
-- Exception handling section: error handling statements.
END;

Example: The following steps are required to create the BEFORE INSERT trigger:

1. Enter the following statement in SQL*Plus to create the table Orders:

CREATE TABLE Orders
(
order_id NUMBER(5),
quantity NUMBER(4),
cost_per_item NUMBER(6,2),
total_cost NUMBER(8,2),
create_date DATE,
created_by VARCHAR2(10)
);

2. Open an editor (Notepad), enter the following code to create the BEFORE INSERT
trigger, and then save the file at C:\oracle_11g\c08_oracle_11g with file name
orders_before_insert.txt. Alternatively, enter the following code directly in SQL*Plus:

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
ON Orders
FOR EACH ROW
DECLARE
v_username VARCHAR2(10);
v_Date DATE;
BEGIN
SELECT User, SYSDATE INTO v_username, v_Date FROM dual;
:NEW.create_date := v_Date;
:NEW.created_by := v_username;
END;
/

In the BEGIN section, the correlation name NEW is used to access and assign the values to the columns create_date and created_by of the table Orders.

3. Now, execute the contents of file to create the trigger. To do so, enter the following command in SQL *Plus:

SQL>@ ‘C:\oracle_11g\c08_oracle_11g\orders_before_insert.txt’

After executing the order_before_insert trigger, it will be saved to the Oracle database and will be fired before the trigger statement is issued.

4. Next, enter the following statement to insert a row into the Orders table.

INSERT INTO Orders VALUES(1, 20, 5, 100, '13-Oct-09', ‘Robert’);

Again, enter the following statement to check the data of the Orders tables.

SELECT * FROM Orders;