TRIGGER

What is trigger ? Explain with an example

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

The Syntax for creating a trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name
[BEFORE | AFTER | INSTEAD OF ]
[INSERT [OR] | UPDATE [OR] | DELETE}]
ON table_name
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- Sql statements
END;

For Example: The price of a product changes constantly. It is important to maintain the history of the prices of the products.

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table

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

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

2) Create the price_history_trigger and execute it.

CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE OF unit_price
ON product
FOR EACH ROW
BEGIN
INSERT INTO product_price_history
VALUES
(:old.product_id,
:old.product_name,
:old.supplier_name,
:old.unit_price);
END;

/

3) Lets update the price of a product.

UPDATE PRODUCT SET unit_price = 800 WHERE product_id = 100

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.