INSTEAD OF triggers are essentially alternatives to DML triggers. They fire when inserts, updates, and deletes are about to occur; your code specifies what to do in place of these DML operations. INSTEAD OF triggers control operations on views,not tables.They can be used to make nonupdateable views updateable and to override the behavior of views that are updateable.
To be modifiable, a join view must NOT contain any of the following :-
• Hierarchical query clauses, such as START WITH or CONNECT BY.
• GROUP BY or HAVING clauses.
• Aggregate functions, such as AVG, COUNT, MAX, MIN, SUM, and so forth.
• Set operations, such as UNION, UNION ALL, INTERSECT, MINUS.
• The DISTINCT operator.
• The ROWNUM pseudocolumn.
INSTEAD OF triggers control insert, update, merge, and delete operations on views, not tables.
INSTEAD OF Triggers works on Nested Tables.
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF operation
ON view_name
FOR EACH ROW
BEGIN
…code goes here…
END;
SQL> CREATE TABLE area
2 (
3 area_id NUMBER,
4 area_desc VARCHAR2(30)
5 );
Table created.
SQL> CREATE TABLE delivery
2 (
3 delivery_id NUMBER,
4 delivery_start DATE,
5 delivery_END DATE,
6 area_id NUMBER,
7 driver_id NUMBER
8 );
Table created.
SQL> CREATE TABLE driver
2 (
3 driver_id NUMBER,
4 driver_name VARCHAR2(30)
5 );
Table created.
SQL> CREATE SEQUENCE delivery_id_seq;
Sequence created.
SQL> CREATE SEQUENCE area_id_seq;
Sequence created.
SQL> CREATE SEQUENCE driver_id_seq;
Sequence created.
SQL> CREATE OR REPLACE VIEW delivery_info AS
2 SELECT d.delivery_id,
3 d.delivery_start,
4 d.delivery_END,
5 a.area_desc,
6 dr.driver_name
7 FROM delivery d,
8 area a,
9 driver dr
10 WHERE a.area_id = d.area_id
11 AND dr.driver_id = d.driver_id;
View created.
SQL> SELECT * FROM area;
no rows selected
SQL> SELECT * FROM delivery;
no rows selected
SQL> SELECT * FROM driver;
no rows selected
SQL> SELECT * FROM delivery_info;
no rows selected
--I cannot directly issue DML statements against the view; it is a join of multiple tables. How would the database know what to do with an INSERT? In fact, I need to tell the database very explicitly what to do when an insert, update, or delete is issued against the DELIVERY_INFO view; in other words, I need to tell it what to do instead of trying to insert, update, or delete. Thus, I will use INSTEAD OF triggers.
SQL> CREATE OR REPLACE TRIGGER delivery_info_insert
2 INSTEAD OF INSERT ON delivery_info
3 DECLARE
4 CURSOR curs_get_driver_id ( cp_driver_name VARCHAR2 ) IS
5 SELECT driver_id
6 FROM driver
7 WHERE driver_name = cp_driver_name;
8
9 v_driver_id NUMBER;
10
11 CURSOR curs_get_area_id ( cp_area_desc VARCHAR2 ) IS
12 SELECT area_id
13 FROM area
14 WHERE area_desc = cp_area_desc;
15
16 v_area_id NUMBER;
17 BEGIN
18 IF :NEW.delivery_END IS NOT NULL THEN
19 RAISE_APPLICATION_ERROR(-20000,'Delivery END Date Value Must Be NULL When Delivery Created');
20 END IF;
21
22 OPEN curs_get_driver_id(UPPER(:NEW.driver_name));
23
24 FETCH curs_get_driver_id INTO v_driver_id;
25
26 IF curs_get_driver_id%NOTFOUND THEN
27 SELECT driver_id_seq.NEXTVAL
28 INTO v_driver_id
29 FROM DUAL;
30
31 INSERT INTO driver(driver_id,driver_name) VALUES(v_driver_id,UPPER(:NEW.driver_name));
32 END IF;
33 CLOSE curs_get_driver_id;
34
35 OPEN curs_get_area_id(UPPER(:NEW.area_desc));
36
37 FETCH curs_get_area_id INTO v_area_id;
38
39 IF curs_get_area_id%NOTFOUND THEN
40 SELECT area_id_seq.NEXTVAL
41 INTO v_area_id
42 FROM DUAL;
43
44 INSERT INTO area(area_id,area_desc) VALUES(v_area_id,UPPER(:NEW.area_desc));
45 END IF;
46
47 CLOSE curs_get_area_id;
48
49 INSERT INTO delivery(delivery_id,
50 delivery_start,
51 delivery_END,
52 area_id,
53 driver_id)
54 VALUES(delivery_id_seq.nextval,
55 NVL(:NEW.delivery_start,SYSDATE),
56 NULL,
57 v_area_id,
58 v_driver_id);
59
60 END;
61 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER delivery_info_update
2 INSTEAD OF UPDATE ON delivery_info
3 DECLARE
4 CURSOR curs_get_delivery ( cp_delivery_id NUMBER ) IS
5 SELECT delivery_END
6 FROM delivery
7 WHERE delivery_id = cp_delivery_id
8 FOR UPDATE OF delivery_END;
9
10 v_delivery_END DATE;
11
12 BEGIN
13 OPEN curs_get_delivery(:NEW.delivery_id);
14
15 FETCH curs_get_delivery INTO v_delivery_END;
16
17 IF v_delivery_END IS NOT NULL THEN
18 RAISE_APPLICATION_ERROR(-20000,'The Delivery END Date Has Already Been Set');
19 ELSE
20 UPDATE delivery
21 SET delivery_END = :NEW.delivery_END
22 WHERE CURRENT OF curs_get_delivery;
23 END IF;
24
25 CLOSE curs_get_delivery;
26
27 END;
28 /
Trigger created.
SQL> CREATE OR REPLACE TRIGGER delivery_info_delete
2 INSTEAD OF DELETE
3 ON delivery_info
4 BEGIN
5 IF :NEW.delivery_END IS NOT NULL THEN
6 RAISE_APPLICATION_ERROR(-20000,'Completed Deliveries Cannot Be Deleted');
7 END IF;
8
9 DELETE FROM delivery
10 WHERE delivery_id = :OLD.delivery_id;
11 END;
12 /
Trigger created.
SQL> INSERT INTO delivery_info(delivery_id,delivery_start,delivery_END,area_desc,driver_name) VALUES (NULL,NULL,NULL,'LOCAL COLLEGE','BIG TED');
1 row created.
SQL> SELECT * FROM area;
AREA_ID AREA_DESC
---------- ------------------------------
1 LOCAL COLLEGE
SQL> SELECT * FROM delivery;
DELIVERY_ID DELIVERY_ DELIVERY_ AREA_ID DRIVER_ID
----------- --------- --------- ---------- ----------
1 16-APR-20 1 1
SQL> SELECT * FROM driver;
DRIVER_ID DRIVER_NAME
---------- ------------------------------
1 BIG TED
SQL> SELECT * FROM delivery_info;
DELIVERY_ID DELIVERY_ DELIVERY_ AREA_DESC DRIVER_NAME
----------- --------- --------- ------------------------------ --------------
1 16-APR-20 LOCAL COLLEGE BIG TED
SQL> UPDATE delivery_info SET delivery_END = SYSDATE + 1 WHERE delivery_id = 1;
1 row updated.
SQL> SELECT * FROM area;
AREA_ID AREA_DESC
---------- ------------------------------
1 LOCAL COLLEGE
SQL> SELECT * FROM delivery;
DELIVERY_ID DELIVERY_ DELIVERY_ AREA_ID DRIVER_ID
----------- --------- --------- ---------- ----------
1 16-APR-20 17-APR-20 1 1
SQL> SELECT * FROM driver;
DRIVER_ID DRIVER_NAME
---------- ------------------------------
1 BIG TED
SQL> SELECT * FROM delivery_info;
DELIVERY_ID DELIVERY_ DELIVERY_ AREA_DESC DRIVER_NAME
----------- --------- --------- ------------------------------ -----------------
1 16-APR-20 17-APR-20 LOCAL COLLEGE BIG TED
SQL> DELETE FROM delivery_info WHERE delivery_id = 1;
1 row deleted.
SQL> SELECT * FROM area;
AREA_ID AREA_DESC
---------- ------------------------------
1 LOCAL COLLEGE
SQL> SELECT * FROM delivery;
no rows selected
SQL> SELECT * FROM driver;
DRIVER_ID DRIVER_NAME
---------- ------------------------------
1 BIG TED
SQL> SELECT * FROM delivery_info;
no rows selected 
