Oracle PL/SQL/Trigger/Trigger on View
Create trigger on a view
<source lang="sql">
SQL> SQL> CREATE TABLE employee(
2 emp_id INTEGER, 3 emp_name VARCHAR2(32), 4 supervised_by INTEGER, 5 pay_rate NUMBER(9,2), 6 pay_type CHAR);
Table created. SQL> CREATE TABLE department
2 (dept_id INTEGER, 3 dept_name VARCHAR2(32));
Table created. SQL> SQL> CREATE OR REPLACE VIEW emp_public_data AS
2 SELECT e.emp_id, 3 e.emp_name, 4 d.dept_name 5 FROM employee e, 6 department d;
View created. SQL> CREATE OR REPLACE TRIGGER emp_name_change
2 INSTEAD OF UPDATE ON emp_public_data 3 BEGIN 4 IF (:new.emp_name <> :old.emp_name) 5 OR (:old.emp_name IS NULL AND :new.emp_name IS NOT NULL) THEN 6 UPDATE employee 7 SET emp_name = :new.emp_name 8 WHERE emp_id = :new.emp_id; 9 END IF; 10 END; 11 /
Trigger created. SQL> SQL> drop table employee; Table dropped. SQL> drop table department; Table dropped. SQL> SQL>
</source>
Use an instead-of trigger.
<source lang="sql">
SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY, 3 building VARCHAR2(15), 4 room_number NUMBER(4), 5 number_seats NUMBER(4), 6 description VARCHAR2(50) 7 );
Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created. SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE VIEW session_place AS
2 SELECT department, course, building, room_number 3 FROM place, session 4 WHERE place.room_id = session.room_id;
View created. SQL> SQL> SELECT * FROM session_place; DEP COURSE BUILDING ROOM_NUMBER --- ---------- --------------- ----------- HIS 301 Building 6 160 CS 101 Building 7 201 ECN 203 Building 6 101 CS 102 Building 6 150 MUS 410 Building 6 170 ECN 101 Music Building 200 NUT 307 Building 7 300 7 rows selected. SQL> SQL> CREATE TRIGGER sessionRoomsInsert
2 INSTEAD OF INSERT ON session_place 3 DECLARE 4 v_roomID place.room_id%TYPE; 5 BEGIN 6 SELECT room_id 7 INTO v_roomID 8 FROM place 9 WHERE building = :new.building 10 AND room_number = :new.room_number; 11 12 UPDATE session 13 SET room_id = v_roomID 14 WHERE department = :new.department 15 AND course = :new.course; 16 END sessionRoomsInsert; 17 /
Trigger created. SQL> SQL> INSERT INTO session_place (department, course, building, room_number)
2 VALUES ("MUS", 100, "Music Building", 200);
1 row created. SQL> SQL> SELECT * FROM session_place; DEP COURSE BUILDING ROOM_NUMBER --- ---------- --------------- ----------- HIS 301 Building 6 160 CS 101 Building 7 201 ECN 203 Building 6 101 CS 102 Building 6 150 MUS 410 Building 6 170 ECN 101 Music Building 200 NUT 307 Building 7 300 MUS 100 Music Building 200 8 rows selected. SQL> SQL> drop view session_place; View dropped. SQL> drop table session; Table dropped. SQL> drop table place; Table dropped. SQL>
</source>
