Triggers in Pl/SQL

As we have event in SOA in a similar way we have triggers in pl/sql

Triggers cause a function/program to be called when a particular event occurs.

Lets understand this example.

The first thing you have know is that you can not have trigger on object hold be sys schema

so connect to the db using a different schema other than sys

now create two tables in the same


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

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

Insert a dummy value in the new product table

INSERT INTO NEW_PRODUCT VALUES('100','test','ABC','420');

Now the functionality here is that when ever some one will update the new product table the values of the existing changed component should be copied to old product table.

To achieve this target we will use a trigger.

CREATE or REPLACE TRIGGER price_trigger
BEFORE UPDATE OF unit_price
ON New_product
FOR EACH ROW
BEGIN
INSERT INTO Old_product
VALUES
(:old.product_id,
:old.product_name,
:old.supplier_name,
:old.unit_price);
END;
/

It says when ever the price is updated in the new product table copy that record in the old product table

We will now try to understand it by updating the new product table

UPDATE NEW_PRODUCT SET unit_price = 800 WHERE product_id = 100

Now as soon as you will execute this command

two things will happen

First the new product table will be updated and a new row will be inserted in the old product table

the new product table will contain a unit price of 800 however the old product table will contain a unit price value of 420