Threaded Mode | Linear Mode

yoga · 11-15-2009, 05:04 AM

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.

Syntax of Triggers

The Syntax for creating a trigger is:

Code:

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;

* CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
* {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
* {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
* [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
* CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
* [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
* [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
* [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
* WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

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

Code:

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.

Code:

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.

Code:

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.

Possibly Related Threads...
Thread:		Author	Replies:	Views:	Last Post
	CYCLIC CASCADING in a TRIGGER	yoga	0	240	11-15-2009 05:07 AM Last Post: yoga
	PL/SQL Trigger Execution Hierarchy	yoga	0	227	11-15-2009 05:06 AM Last Post: yoga