PL/SQL Triggers

A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur. A trigger is a named PL/SQL unit that is stored in the database and can be invoked repeatedly when some events occur.

You can enable and disable a trigger, but you cannot explicitly invoke it. While a trigger is enabled, the database automatically invokes it that is, the trigger fires whenever its triggering event occurs. While a trigger is disabled, it does not fire.

You can write triggers that fire whenever one of the following operations occurs.

  • DML statements (INSERT, UPDATE, DELETE)
  • DDL statements (CREATE, ALTER or DROP)
  • Database events, such as login/logoff, errors, or startup/shutdown

Use of Triggers:

  • Automatically generate derived column values
  • Provide auditing
  • Prevent invalid transactions
  • Gather statistics on table access
  • Modify table data when DML statements are issued against views
  • Enforce complex security authorizations
  • Enforce referential integrity
  • Enforce complex business rules
  • Provide transparent event logging
  • Publish information about database events, user events, and SQL statements to subscribing applications

Types of Triggers

  • Row Triggers and Statement Triggers
  • BEFORE and AFTER Triggers
  • INSTEAD OF Triggers
  • Triggers on System Events and User Events

Row Triggers:

A row trigger is fired each time the table is affected by the triggering statement.
For example,
If an UPDATE statement updates multiple rows of a table, then row trigger is fired for each row affected by the UPDATE statement. If a triggering statement affects zero rows, then trigger won’t run.

Statement Triggers:

A statement trigger is fired only once when the table is affected by the triggering statement, regardless of the number of rows affected the triggering statement. Even if zero rows are affected, the trigger will run.

BEFORE Triggers:

BEFORE triggers run the trigger action before the triggering statement is run.

AFTER Triggers:

AFTER triggers run the trigger action after the triggering statement is run.

INSTEAD OF Triggers:

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements.

These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

Triggers on System Events and User Events

You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:

System events

  • Database startup and shutdown
  • Data Guard role transitions
  • Server error message events
  • User events
  • User login and logoff

Triggers on system events can be defined at the database level or schema level. The DBMS_AQ package is one example of using database triggers to perform certain actions. For example, a database shutdown trigger is defined at the database level:

CREATE TRIGGER register_shutdown
ON DATABASE SHUTDOWN
BEGIN
...
DBMS_AQ.ENQUEUE(...);
...
END;

The syntax of Triggers Syntax for Creating a Trigger

CREATE [OR REPLACE ]
    TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]  ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;

Example 1 :

BEFORE UPDATE FOR EACH ROW

1) Create the ‘product’ and ‘product_price_history’ table

CREATE TABLE product
(id number(5),
name varchar2(32),
supplier varchar2(32),
price number(7,2)
);

CREATE TABLE product_price_history
(product_id number(5),
product_name varchar2(32),
supplier_name varchar2(32),
unit_price number(7,2)
);


Table PRODUCT created.
Table PRODUCT_PRICE_HISTORY created.

2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE OF price ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
VALUES(
:old.id,
:old.name,
:old.supplier,
:old.price
);
END;
/

3) Let’s update the price of a product.

UPDATE PRODUCT SET price = 500 WHERE id = 101;

Once the above update query is executed, the trigger fires and updates the ‘product_price_history’ table.

4)If you ROLLBACK the transaction before committing to the database, the data inserted to the table is also rolled back.

Example 2:

AFTER UPDATE FOR EACH ROW

1. Create Emp_log table.

CREATE TABLE Emp_log
(Emp_id NUMBER,
Log_date DATE,
New_salary NUMBER,
Action VARCHAR2(20)
);

2. Create the log_salary_increase trigger and execute it.

CREATE OR REPLACE TRIGGER log_salary_increase
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO Emp_log
(Emp_id, Log_date, New_salary, Action)
VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/

3. Update salary of an employee

UPDATE employeesSET salary = salary + 1000.0WHERE Department_id = 20;

4. Check Emg_log data.

SELECT * FROM Emp_log;
    EMP_ID LOG_DATE  NEW_SALARY ACTION
---------- --------- ---------- --------------------
101        22-SEP-16   10049.50 New Salary
102        22-SEP-16    6900.00 New Salary
2 rows selected.

Example 3:

Conditional Trigger Prints Salary Change Information

CREATE OR REPLACE TRIGGER print_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON employees
FOR EACH ROW
WHEN (NEW.job_id <> 'AD_PRES') -- do not print information about President
DECLARE
sal_diff NUMBER;
BEGIN sal_diff := :NEW.salary - :OLD.salary;
DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/

 

UPDATE employeesSET salary = salary * 1.05

WHERE department_id IN (10, 20, 90);
Whalen: Old salary = 2800, New salary = 2940, Difference: 140
Hartstein: Old salary = 13000, New salary = 13650, Difference: 650
Fay: Old salary = 6000, New salary = 6300, Difference: 300
Kochhar: Old salary = 17000, New salary = 17850, Difference: 850
De Haan: Old salary = 17000, New salary = 17850, Difference: 850
6 rows updated.

Example 4:

CREATE TABLE product_check
(Message varchar2(50),
Current_Date number(32));

 

BEFORE UPDATE, Statement-Level:

This trigger will insert a record into the table ‘product_check’ before an SQL update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER Before_Update_Stat_product
BEFORE UPDATE ON product_check
Begin
INSERT INTO product_check
Values('Before update, statement level',sysdate);
END;
/

Example 5:

BEFORE UPDATE, Row Level:

This trigger will insert a record into the table ‘product_check’ before each row is updated.

CREATE or REPLACE TRIGGER Before_Upddate_Row_product
BEFORE UPDATE ON product_check
FOR EACH ROW
BEGIN INSERT INTO product_check
Values('Before update row level',sysdate);
END;
/

Example 6:

AFTER UPDATE, Statement-Level:

This trigger will insert a record into the table ‘product_check’ after a SQL update statement is executed, at the statement level.

CREATE or REPLACE TRIGGER After_Update_Stat_product
AFTER UPDATE ON product_check
BEGIN
INSERT INTO product_check
Values('After update, statement level', sysdate);
End;
/

Example 7:

AFTER UPDATE, Row Level:

This trigger will insert a record into the table ‘product_check’ after each row is updated.

CREATE or REPLACE TRIGGER After_Update_Row_product
AFTER insert On product_check
FOR EACH ROW
BEGIN
INSERT INTO product_check
Values('After update, Row level',sysdate);
END;
/

Now let’s execute an update statement on table product.

UPDATE PRODUCT SET unit_price = 800
WHERE product_id in (100,101); 

Let’s check the data in ‘product_check’ table to see the order in which the trigger is fired.

SELECT * FROM product_check;
Mesage                               Current_Date
---------------------------------   -------------
Before update, statement level        22-SEP-2016
Before update, row level              22-SEP-2016
After update, Row level               22-SEP-2016
Before update, row level              22-SEP-2016
After update, Row level               22-SEP-2016
After update, statement level         22-SEP-2016

Example 7:

Trigger with REFERENCING Clause

creates a table with the same name as a correlation name, new, and then creates a trigger on that table. To avoid conflict between the table name and the correlation name, the trigger references the correlation name as Newest.

CREATE TABLE new
(field1 NUMBER,
field2 VARCHAR2(20));

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON new
REFERENCING new AS Newest
FOR EACH ROW
BEGIN
:Newest.Field2 := TO_CHAR (:newest.field1);
END;
/

How To know Information about Triggers.

We can use the data dictionary view ‘USER_TRIGGERS’ to obtain information about any trigger.

This view stores information about the header and body of the trigger.

SELECT * FROM user_triggers
WHERE trigger_name = 'Before_Update_Stat_product';

The above SQL query provides the header and body of the trigger ‘Before_Update_Stat_product’.

You can drop a trigger using the following command.

DROP TRIGGER trigger_name;