Oracle PL/SQL/Trigger/Trigger on View
Create trigger on a view
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>
Use an instead-of trigger.
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>