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>
--------------------
| Order of trigger firing. |
|
|
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>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> CREATE SEQUENCE trig_seq
2 START WITH 1
3 INCREMENT BY 1;
Sequence created.
SQL>
SQL> CREATE OR REPLACE PACKAGE TrigPackage AS
2 v_Counter NUMBER;
3 END TrigPackage;
4 /
Package created.
SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeUpdate
2 BEFORE UPDATE ON session
3 BEGIN
4 TrigPackage.v_Counter := 0;
5
6 INSERT INTO MyTable (num_col, char_col)
7 VALUES (trig_seq.NEXTVAL,
8 'Before Statement: counter = ' || TrigPackage.v_Counter);
9
10 TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
11 END sessionBeforeUpdate;
12 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER sessionAfterStatement1
2 AFTER UPDATE ON session
3 BEGIN
4 INSERT INTO MyTable (num_col, char_col)
5 VALUES (trig_seq.NEXTVAL,
6 'After Statement 1: counter = ' || TrigPackage.v_Counter);
7
8 TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
9 END sessionAfterStatement1;
10 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER sessionAfterStatement2
2 AFTER UPDATE ON session
3 BEGIN
4 INSERT INTO MyTable (num_col, char_col)
5 VALUES (trig_seq.NEXTVAL,'After Statement 2: counter = ' || TrigPackage.v_Counter);
6
7 TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
8 END sessionAfterStatement2;
9 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeRow1
2 BEFORE UPDATE ON session
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO MyTable (num_col, char_col)
6 VALUES (trig_seq.NEXTVAL,
7 'Before Row 1: counter = ' || TrigPackage.v_Counter);
8
9 -- Increment for the next trigger.
10 TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
11 END sessionBeforeRow1;
12 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeRow2
2 BEFORE UPDATE ON session
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO MyTable (num_col, char_col)
6 VALUES (trig_seq.NEXTVAL,
7 'Before Row 2: counter = ' || TrigPackage.v_Counter);
8
9 TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
10 END sessionBeforeRow2;
11 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER sessionBeforeRow3
2 BEFORE UPDATE ON session
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO MyTable (num_col, char_col)
6 VALUES (trig_seq.NEXTVAL,
7 'Before Row 3: counter = ' || TrigPackage.v_Counter);
8
9 TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
10 END sessionBeforeRow3;
11 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER sessionAfterRow
2 AFTER UPDATE ON session
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO MyTable (num_col, char_col)
6 VALUES (trig_seq.NEXTVAL,
7 'After Row: counter = ' || TrigPackage.v_Counter);
8
9 TrigPackage.v_Counter := TrigPackage.v_Counter + 1;
10 END sessionAfterRow;
11 /
Trigger created.
SQL>
SQL> DELETE FROM MyTable;
0 rows deleted.
SQL>
SQL> UPDATE session
2 SET num_credits = 4
3 WHERE department IN ('HIS', 'CS');
4 rows updated.
SQL>
SQL> SELECT *
2 FROM MyTable
3 ORDER BY num_col;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
1 Before Statement: counter = 0
2 Before Row 3: counter = 1
3 Before Row 2: counter = 2
4 Before Row 1: counter = 3
5 After Row: counter = 4
6 Before Row 3: counter = 5
7 Before Row 2: counter = 6
8 Before Row 1: counter = 7
9 After Row: counter = 8
10 Before Row 3: counter = 9
11 Before Row 2: counter = 10
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
12 Before Row 1: counter = 11
13 After Row: counter = 12
14 Before Row 3: counter = 13
15 Before Row 2: counter = 14
16 Before Row 1: counter = 15
17 After Row: counter = 16
18 After Statement 2: counter = 17
19 After Statement 1: counter = 18
19 rows selected.
SQL>
SQL>
SQL> DROP SEQUENCE trig_seq;
Sequence dropped.
SQL>
SQL> drop table mytable;
Table dropped.
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>
|
|
------------------------------
| Avoid the mutating table error. |
|
|
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, 'Scott', 'Lawson','Computer Science', 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, 'Mar', 'Wells','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, 'Jone', 'Bliss','Computer Science', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, 'Man', 'Kyte','Economics', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, 'Pat', 'Poll','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, 'Tim', 'Viper','History', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, 'Barbara', 'Blues','Economics', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, 'David', 'Large','Music', 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, 'Chris', 'Elegant','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, 'Rose', 'Bond','Music', 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, 'Rita', 'Johnson','Nutrition', 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, 'Sharon', 'Clear','Computer Science', 3);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE StudentData AS
2 TYPE t_Majors IS TABLE OF lecturer.major%TYPE
3 INDEX BY BINARY_INTEGER;
4 TYPE t_IDs IS TABLE OF lecturer.ID%TYPE
5 INDEX BY BINARY_INTEGER;
6
7 myLecturerMajors t_Majors;
8 myLecturerIDs t_IDs;
9 v_NumEntries BINARY_INTEGER := 0;
10 END StudentData;
11 /
Package created.
SQL>
SQL> CREATE OR REPLACE TRIGGER RLimitMajors
2 BEFORE INSERT OR UPDATE OF major ON lecturer
3 FOR EACH ROW
4 BEGIN
5 StudentData.v_NumEntries := StudentData.v_NumEntries + 1;
6 StudentData.myLecturerMajors(StudentData.v_NumEntries) :=
7 :new.major;
8 StudentData.myLecturerIDs(StudentData.v_NumEntries) := :new.id;
9 END RLimitMajors;
10 /
Trigger created.
SQL>
SQL> CREATE OR REPLACE TRIGGER SLimitMajors
2 AFTER INSERT OR UPDATE OF major ON lecturer
3 DECLARE
4 studentMax CONSTANT NUMBER := 5;
5 studentCount NUMBER;
6 myLecturerID lecturer.ID%TYPE;
7 v_Major lecturer.major%TYPE;
8 BEGIN
9 FOR v_LoopIndex IN 1..StudentData.v_NumEntries LOOP
10 myLecturerID := StudentData.myLecturerIDs(v_LoopIndex);
11 v_Major := StudentData.myLecturerMajors(v_LoopIndex);
12
13 SELECT COUNT(*)
14 INTO studentCount
15 FROM lecturer
16 WHERE major = v_Major;
17
18 IF studentCount > studentMax THEN
19 RAISE_APPLICATION_ERROR(-20000,
20 'Too many lecturer for major ' || v_Major ||
21 ' because of student ' || myLecturerID);
22 END IF;
23 END LOOP;
24
25 StudentData.v_NumEntries := 0;
26 END SLimitMajors;
27 /
Trigger created.
SQL>
SQL> UPDATE lecturer
2 SET major = 'History'
3 WHERE ID = 10003;
1 row updated.
SQL>
SQL> UPDATE lecturer
2 SET major = 'History'
3 WHERE ID = 10002;
1 row updated.
SQL>
SQL> UPDATE lecturer
2 SET major = 'History'
3 WHERE ID = 10009;
1 row updated.
SQL>
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
|
|
--------------------------
| Log all triggerable action: iinsert, update and delete to a log table |
|
|
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ('01','Jason', 'Martin', to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto', 'Programmer')
3 /
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('02','Alison', 'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
3 /
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('03','James', 'Smith', to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
3 /
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('04','Celia', 'Rice', to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
3 /
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('05','Robert', 'Black', to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
3 /
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('06','Linda', 'Green', to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York', 'Tester')
3 /
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('07','David', 'Larry', to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York', 'Manager')
3 /
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values('08','James', 'Cat', to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 1232.78,'Vancouver', 'Tester')
3 /
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
SQL>
SQL>
SQL>
SQL> create table employees_copy as select * from employee;
SQL>
SQL> create table employees_log(
2 who varchar2(30),
3 action varchar2(100),
4 when date );
SQL>
SQL>
SQL> create or replace trigger biud_employees_copy
2 before insert or update or delete
3 on employees_copy
4 begin
5 state_package.rows_changed := 0;
6 end;
7 /
SQL>
SQL> create trigger biudfer_employees_copy
2 before insert or update or delete
3 on employees_copy
4 for each row
5 declare
6 l_action employees_log.action%type;
7 begin
8 if INSERTING then
9 l_action := 'Insert';
10 elsif UPDATING then
11 l_action := 'Update';
12 elsif DELETING then
13 l_action := 'Delete';
14 else
15 raise_application_error( -20001,
16 'You should never ever get this error.' );
17 end if;
18
19 state_package.rows_changed := state_package.rows_changed + 1;
20
21 if UPDATING( 'SALARY' ) then
22 l_action := l_action || ' - ' ||
23 'Salary for id ' || :old.id ||
24 ' changed from ' || :old.salary ||
25 ' to ' || :new.salary;
26 end if;
27
28 insert into employees_log(
29 who, action, when )
30 values(
31 user, l_action, sysdate );
32 end;
33 /
SQL>
SQL> create trigger aiud_employees_copy
2 after insert or update or delete
3 on employees_copy
4 declare
5 l_action employees_log.action%type;
6 begin
7 if INSERTING then
8 l_action := state_package.rows_changed || ' were ' || 'inserted';
9 elsif UPDATING then
10 l_action := state_package.rows_changed || ' were ' || 'updated';
11 elsif DELETING then
12 l_action := state_package.rows_changed || ' were ' || 'deleted';
13 else
14 raise_application_error( -20001,
15 'You should never ever get this error.' );
16 end if;
17
18 insert into employees_log(
19 who, action, when )
20 values(
21 user, l_action, sysdate );
22 end;
23 /
SQL>
SQL> update employees_copy set salary = salary * 0.95;
SQL>
SQL>
SQL> select * from employees_log;
WHO ACTION WHEN
------------------------------ ---------------------------------------------------------------------------------------------------- ---------
JAVA2S Update - Salary for id 01 changed from 1234.56 to 1172.83 09-SEP-06
JAVA2S Update - Salary for id 02 changed from 6661.78 to 6328.69 09-SEP-06
JAVA2S Update - Salary for id 03 changed from 6544.78 to 6217.54 09-SEP-06
JAVA2S Update - Salary for id 04 changed from 2344.78 to 2227.54 09-SEP-06
JAVA2S Update - Salary for id 05 changed from 2334.78 to 2218.04 09-SEP-06
JAVA2S Update - Salary for id 06 changed from 4322.78 to 4106.64 09-SEP-06
JAVA2S Update - Salary for id 07 changed from 7897.78 to 7502.89 09-SEP-06
JAVA2S Update - Salary for id 08 changed from 1232.78 to 1171.14 09-SEP-06
JAVA2S 8 were updated 09-SEP-06
SQL>
SQL>
SQL> drop table employees_copy;
SQL>
SQL> drop table employees_log;
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
SQL>
SQL>
SQL>
SQL>
|
|
=================================
| Check user name in a trigger |
|
|
SQL>
SQL>
SQL> create table foo( a number );
Table created.
SQL>
SQL> create or replace trigger biud_foo
2 before insert or update or delete
3 on foo
4 begin
5 if user not in ( 'CLBECK', 'SDILLON' ) then raise_application_error( -20001,'You do not have access to modify this table.' );
6 end if;
7 end;
8 /
Trigger created.
SQL>
SQL> insert into foo (a) VALUES (1);
insert into foo (a) VALUES (1)
*
ERROR at line 1:
ORA-20001: You do not have access to modify this table.
ORA-06512: at "JAVA2S.BIUD_FOO", line 2
ORA-04088: error during execution of trigger 'JAVA2S.BIUD_FOO'
SQL>
SQL> drop table foo;
Table dropped.
SQL>
------------------------
| A Trigger to check the available room |
|
|
SQL>
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> 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> CREATE OR REPLACE TRIGGER CheckRoomID
2 BEFORE INSERT OR UPDATE OF room_id
3 ON session
4 FOR EACH ROW
5 DECLARE
6 v_RoomID NUMBER(5);
7 BEGIN
8 SELECT room_id
9 into v_RoomID
10 FROM place
11 where room_id = :new.room_id;
12 EXCEPTION
13 WHEN NO_DATA_FOUND THEN
14 RAISE_APPLICATION_ERROR(-20000, :new.room_id || ' is not a ' ||
15 ' valid room');
16 END CheckRoomID;
17 /
Trigger created.
SQL>
SQL>
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);
INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
*
ERROR at line 1:
ORA-20000: 20000 is not a valid room
ORA-06512: at "JAVA2S.CHECKROOMID", line 10
ORA-04088: error during execution of trigger 'JAVA2S.CHECKROOMID'
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);
INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
*
ERROR at line 1:
ORA-20000: is not a valid room
ORA-06512: at "JAVA2S.CHECKROOMID", line 10
ORA-04088: error during execution of trigger 'JAVA2S.CHECKROOMID'
SQL>
SQL>
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table place;
Table dropped.
|
|
-------------------------
|
|
|
No comments:
Post a Comment