INSTEAD OF trigger in plsql

 

The Oracle INSTEAD-OF trigger has the ability to update normally non-updateable views. Simple views are generally updateable via DML statements issued against the view. However, when a view becomes more complex it may lose its “updateable-ness,” and the Oracle INSTEAD-OF trigger must be used.
INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.
If a view is inherently updatable and has INSTEAD OF triggers, then the triggers take preference. In other words, the database fires the triggers instead of performing DML on the view.
Restrictions on INSTEAD OF Triggers

• INSTEAD OF triggers are valid only for views. You cannot specify an INSTEAD OF trigger on a table.
• You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value. 
• In this example, an order_info view is created to display information about customers and their orders:
  

  1	CREATE VIEW order_info AS

  2	SELECT c.customer_id, c.cust_last_name, c.cust_first_name,

  3	o.order_id, o.order_date, o.order_status

  4	FROM customers c, orders o

  5	WHERE c.customer_id = o.customer_id;

  Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view.
  

  01	CREATE OR REPLACE TRIGGER order_info_insert

  02	INSTEAD OF INSERT ON order_info

  03

  DECLARE

  04	duplicate_info EXCEPTION;

  05	PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

  06

  BEGIN

  07	INSERT INTO customers

  08	(customer_id, cust_last_name, cust_first_name)

  09

  VALUES (

  10	:new.customer_id,

  11	:new.cust_last_name,

  12	:new.cust_first_name);

  13

  14	INSERT INTO orders (order_id, order_date, customer_id)

  15

  VALUES (

  16	:new.order_id,

  17	:new.order_date,

  18	:new.customer_id);

  19

  20

  EXCEPTION

  21	WHEN duplicate_info THEN

  22	RAISE_APPLICATION_ERROR (

  23	num=> -20107,

  24	msg=> 'Duplicate customer or order ID');

  25

  26	END order_info_insert;

  You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):
  

  INSERT INTO order_info VALUES
     (999, 'Smith', 'John', 2500, '13-MAR-2001', 0);