Infolinks

Showing posts with label PLSQL. Show all posts
Showing posts with label PLSQL. Show all posts

Wednesday, 11 July 2012

PROCEDURES, FUNCTIONS AND PACKAGES

 
PROCEDURES, FUNCTIONS AND
PACKAGES
What is a stored procedure?
Advantages of stored procedures
Creating a stored procedure
Creating a stored function
Recompiling
Types of parameters
Parameter modes
NCOPY compiler hints
RAISE_APPLICATION_ERROR procedure
Packages
What is a stored procedure?
As we have seen in the introduction to PL/SQL that there are two types of PL/SQL blocks –
anonymous and stored procedures.
A stored procedure is a PL/SQL block that is stored in the database with a name. It is invoked
using the name. Each procedure is meant for a specific purpose.
A stored procedure is stored in the database as an object. It is also called as database
procedure as it is stored in the database.
A procedure may take one or more parameters. If a procedure takes parameters then these
parameters are to be supplied at the time of calling the procedure.
 
What is a function?
A function is similar to procedure, except that it returns a value. The calling program should
use the value returned by the function.
Advantages of stored procedures
Stored procedures and functions offer the following benefits.
Reduced network traffic
Stored procedures are stored in the database on the server. In order to invoke a stored
procedure a client has to use only the name of the stored procedure. This results in reduced
network traffic because only the name is passed to server and not the entire PL/SQL block.
Better Performance
Stored procedures improve performance because the database accessed by stored procedures
and stored procedures themselves are stored in the database. Furthermore, because a stored
procedure’s compiled form is available in the database, no compilation step is required to
execute the code.
Apart from this, stored procedures make use of shared memory. That means if a stored
procedure is called by user-1, then it is brought into server’s memory. As a result there is a
chance of finding the stored procedure in memory, when user-2 later wants to use the same
procedure. In this case stored procedure need not be brought into memory since it is already
in the memory. This saves time and increases performance.
Easy Maintenance
If there is any change in the application, only the concerned stored procedures are to be
modified to incorporate the change. As all the clients access stored procedures, they
automatically use the modified definition. You only have to change business login once in the
stored procedure. Therefore stored procedures improve the integrity and consistency of your
applications.
Security
Stored procedures help enforcement of data security. You can allow users to access only the
stored procedures and not the table that are manipulated by stored procedures.
Whenever a stored procedure runs, it runs with the privileges of the owner and not the user
running it. Because users only have the privilege to execute the procedure and not the
privileges to access the underlying tables, it increases security.
 
Owner rights
When a stored procedure is run under the privileges of the owner of the stored procedure, it is
called as owner rights.
Invoker rights
When a stored procedure is executed with the privileges of the invoker and not under the
privileges of the owner, it is called as invoker rights.
We will discuss about the difference between these two ways of running a stored procedure in
detailed later in this chapter.
Creating a stored procedures
A stored procedure is created using CREATE PROCEDURE command.
CREATE [OR REPLACE] PROCEDURE name [(parameter[,parameter, ...])] {IS
|AS}
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
OR REPLACE
existing.
Oracle creates procedure even though the procedure is not valid but it displays the message
saying that the procedure is created with errors. Then you have to rectify the errors and
recreate the procedure. If OR REPLACE option is not given Oracle doesn’t allow you to replace
an existing stored procedure. So, it is better you use OR REPLACE option so that the existing
invalid version of the procedure will be replaced with the valid version.
is used to create a procedure even a procedure with the same name is already
SHOW ERRORS
During the creation of the procedure if there are any errors Oracle displays the message
saying procedure is created but with errors with the following message:
Warning: Procedure created with compilation errors.

In order to displays errors in the most recent CREATE PROCEDURE statement, use SHOW
ERRORS command.
The following stored procedure inserts a new record into PAYMENTS table with the given roll
number and amount.
create or replace procedure newpayment(rollno number, amt number)
is
begin
insert into payments values(rollno, sysdate,amt);
commit;
end;
/
Once a stored procedure is stored in the database, it can be invoked using EXECUTE command
by using the name of the procedure.
EXECUTE command
A procedure can be executed using EXECUTE command. To execute a procedure either you
must be the owner of the procedure or you must have EXECUTE privilege on the procedure.
The following example shows how to invoke NEWPAYMENT procedure.
SQL> execute newpayment(10,2000);
PL/SQL procedure successfully completed.
In the above example, NEWPAYMENT is invoked by passing 10 and 2000. It inserts a new row
into PAYMENTS table with values 10, sysdate, and 2000.
Creating a stored function
A stored function is same as a procedure, except that it returns a value. CREATE FUNCTION
command is used to create a stored function.
CREATE [OR REPLACE] FUNCTION name
[(parameter[,parameter, ...])]
RETURN datatype
{IS | AS}
[local declarations]
BEGIN
executable statements
RETURN value;
[EXCEPTION
exception handlers]
END [name];
OR REPLACE
exists
is used to create a function even though a function with the same name already
RETURN datatype
specifies the type of data to be returned by the function.
RETURN
type as the return type specified using RETURN option in the header.
The following stored function takes roll number of the student and return the amount yet to be
paid by the student.
statement in the executable part returns the value. The value must be of the same
create or replace function getdueamt(prollno number)
return number
is
v_fee number(5);
v_amtpaid number(5);
begin
-- get total amount paid by student
select sum(amount) into v_amtpaid
from payments
where rollno = prollno;
-- get course fee of the course into which student joined
select fee into v_fee
from courses
where ccode = ( select ccode from batches
where bcode in
( select bcode from students
where rollno = prollno)
);
-- return the difference
return v_fee - v_amtpaid;
end;
/
The above function can be called from a PL/SQL block as follows:
begin
dbms_output.put_line(getdueamt(10));
end;
User-defined PL/SQL functions can be used in SQL in the same manner as the standard
functions such as ROUND and SUBSTR..
 
For example, the function GETDUEAMT can be invoked from SELECT command as follows:
SQL> select rollno,
getdueamt(rollno)
2 from students;
ROLLNO GETDUEAMT(ROLLNO)
--------- -----------------
1 0
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 1500
10 2000
11 0
12 1500
13 0
Getting source code
It is possible to see the source code of stored procedures and function by using
USER_SOURCE data dictionary view.
The following SELECT command will display the source code of NEWPAYMENT stored
procedure.
SQL> select text
2 from user_source
3 where name = 'NEWPAYMENT';
TEXT
------------------------------------------------------------------------
procedure newpayment(rollno number, amt number)
is
begin
insert into payments values(rollno, sysdate,amt);
commit;
end;

Privileges required
To create a stored procedure, you must have CREATE PROCEDURE system privilege.
You must also have required object privileges on the objects that are referred in the procedure
in order to successfully compile the procedure.
Note:
through ROLES.
The owner of the procedure CANNOT obtain required privileges on the stored procedure
Recompiling
Stored procedures and functions are compiled at the time of creation and stored in the
compiled form. If a procedure becomes invalid afterwards, it is to be recompiled before it is
executed. Oracle implicitly compiles the procedure when an invalid procedure is referred.
However, it is possible to explicitly recompile
In order to recompile a stored procedure use ALTER PROCEDURE and ALTER FUNCTION to
recompile a procedure and function respectively.
ALTER PROCEDURE procedurename COMPILE;
ALTER FUNCTION functionname COMPILE;
The following sequence will illustrate the importance of recompilation.
Assume user SCOTT has created NEWPAYMENT procedure as follows
create or replace procedure newpayment(rollno number, amt number)
is
begin
insert into book.payments values(rollno, sysdate,amt);
commit;
end;
/
Since SCOTT doesn’t have INSERT privilege on PAYMENTS table of BOOK, the procedure is
select status from user_objects where object_name = 'NEWPAYMENT';
STATUS
-------
INVALID

Now, user BOOK has granted INSERT privilege on PAYMENTS table to SCOTT as follows:
GRANT INSERT ON PAYMENTS to SCOTT;
Then any subsequent reference to NEAPAYMENT procedure in SCOTT will implicitly
ALTER PROCEDURE newpayment COMPILE;
After recompilation, the procedure will have status VALID as provide by the following
select status from user_objects where object_name = 'NEWPAYMENT';
STATUS
-------
VALID
Types of Parameters
Parameters of a procedure are of two types.
Formal parameters
Actual Parameters
Formal Parameters
The parameters declared in the definition of procedure are called as
They receive the values sent while calling the procedure.
formal parameters.
procedure increase_fee (pccode varchar2, pamt number)
In the above procedure, PCCODE, PAMT parameters are called as formal parameters.
 
Actual Parameters
The values given within parentheses while calling the procedure are called as
parameters
actual.
increase_feepaid ( v_ccode, 2000);
v_ccode and 2000 are actual parameters. These values are copied to the corresponding
formal parameters - pccode and p
amt.
Parameter Modes
Parameter mode is used to specify what can be done with formal parameter. The following are
the available modes.
IN
OUT
IN OUT
IN mode
IN parameters lets you pass a value to the subprogram being called. The value cannot be
changed inside the subprogram. It is like a constant in the subprogram. Therefore it cannot be
assigned a value.
procedure increase_fee (pccode in varchar2, pamt number) is
begin
. . .
end;
The actual parameter corresponding to IN parameter can be a variable, constant, or
expression.
The default parameter mode is IN. In the above example though we didn’t specify the
parameter mode for PAMT is it taken as IN parameter.
OUT Mode
An OUT parameter lets the subprogram pass a value to caller. Inside the subprogram OUT
parameter is an uninitialized variable.
Subprogram has to place a value in the OUT parameters, which will be used by caller program.
Whatever changes are made to formal parameter, the changes will be made available to actual
parameter.

The actual parameter corresponding to OUT parameter must be a variable.
IN OUT Mode
It is same as IN and OUT modes put together. It can get a value from the calling program
and return value to calling program. The value of this type of parameter can be used in the
subprogram and
The actual parameter corresponding to IN OUT parameter must be a variable.
The following procedure takes course code and returns the dates on which the first and last
batches of that course have started.
create or replace procedure get_dates( pccode in varchar2,
first_date out date,
last_date out date) is
begin
select min(stdate) into first_date
from batches
where ccode = pccode;
select max(stdate) into last_date
from batches
where ccode = pccode;
end;
Once the procedure is created, it can be called as follows:
declare
min_date date;
max_date date;
begin
get_dates( ‘ora’, min_date, max_date);
dbms_output.put_line( min_date || ‘:’ || max_date);
end;
The output of the above program will be:
12-JAN-01:15-AUG-01
 
NOCOPY Compiler Hint
By default,
OUT
procedure exits normally, the values assigned to
copied into the corresponding actual parameters.
When the parameters hold large data structures such as records, and instances of object types
(which we will in later chapters), copying slows down execution and uses up more memory. To
prevent that, you can specify the
OUT
OUT and IN OUT parameters are passed by value. That means, the value of an INactual parameter is copied into the corresponding formal parameter. Then, if theOUT and IN OUT formal parameters areNOCOPY hint, which allows the PL/SQL to pass OUT and INparameters by reference.
PROCEDURE change(pstudent IN OUT NOCOPY student_type) IS ...
In the above example parameter PSTUDENT is of object type – STUDENT_TYPE. It is now
passed by reference as we used NOCOPY option with it.
Invoker Rights vs. definer rights
By default, when a user executes a procedure, the procedure is executed with the privileges of
the owner. That means, the privileges of invoking user (invoker) are not taken into account
only the privileges of definer (owner) of the procedure will be considered.
If the procedure is to be called by user other than the owner then the use must be granted
EXECUTE privilege on the procedure.
When the procedure is executed using the privileges of definer then, it is called as definer
rights.
Definer rights is the default option. All object references in the procedure will be referring to
objects of definer and not invoker. Let us see an example to understand this point further.
Assume we have procedure ADDMESSAGE created by user SRIKANTH as follows:
create or replace procedure addmessage(msg varchar2)
is
begin
insert into messages values (msg, sysdate);
commit;
end;
Then user SRIKANTH has granted EXECUTE privilege to user PRANEETH as follows:
grant execute on addmessage to praneeth;

Now user PRANEETH can execute the procedure as follows:
execute Srikanth.addmessage('First message');
The message “First message” is inserted into MESSAGES table of SRIKANTH – the definer of
the procedure.
What if user PRANEETH also has MESSAGE table with the same structure as MESSAGES table
of SRIKANTH? The answer is; even now the message goes to MESSAGES table of SRIKANTH,
since all references to objects in the procedure are resolved to definer of the procedure.
Invoke Rights
Oracle8i has introduced invoker rights. In case of invoker rights, procedure is executed with
the privileges of invoker and not the definer of the procedure.
If you want the procedure to be executed with invoker rights and not with definer right, then
the procedure is to be created with AUTHID CURRENT_USER option as follows.
create or replace procedure addmessage(msg varchar2)
authid current_user as
is
begin
insert into messages values (msg, sysdate);
commit;
end;
AUTHID CURRENT_USER option specifies that the procedure to be executed under the
privileges of invoking user. Also remember that all object references are resolved to invoker
and not the definer. However, if any object is qualified with schema (username) then it will
refer to object in that schema. In other words, all unqualified object are taken from invoker’s
schema.
Now if you execute ADDMESSAGE procedure from user PRANEETH, it will fail, as there is no
MESSAGES table in that schema.
SQL> execute srikanth.addmessage('Second message');
BEGIN srikanth.addmessage('fine'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SRIKANTH.ADDMESSAGE", line 4
ORA-06512: at line 1
 
As you can understand, the above message is complaining about missing MESSAGES table. If
you create MESSAGES table in PRANEETH schema, then the procedure will succeed and the
message is inserted into MESSAGES table of PRANEETH account.
The advantage of invoker rights is that it allows you to centralized code whereas the data is
stored in individual schema. That means though users use the same common code the data is
stored in objects of their schema.
RAISE_APPLICATION_ERROR Procedure
This procedure is used to create your own application error numbers and messages.
When you have to terminate a PL/SQL program, you can display an error message and send
error code to host environment using this procedure.
RAISE_APPLICATION_ERROR (errornumber, errormessage);
errornumber
is a number in the range -20001 and -20999.
errormessage
The following PL/SQL block displays the error message along with error number (-20100)
when NO_DATA_FOUND exception is raised.
is the message to be displayed.
declare
v_fee courses.fee%type;
begin
select fee into v_fee
from courses
where ccode = ‘ora’;
-- remaining statements
exception
when no_data_found then
raise_application_error(-20100,‘Invalid course code’);
end;
/
Packages
A package is a collection of related procedures, functions, variables and data types. A
package typically contains two parts – specification and body.
 
Specification
Package specification contains declarations for items, procedure and functions that are to be
made public. All public objects of package are visible outside the package. In other words,
public objects are callable from outside of the package.
Private items of the package can be used only in the package and not outside the package.
The following is the syntax to create package specification.
CREATE PACKAGE package_name AS
/* declare public objects of package */
END;
Body
Body of the package defines all the objects of the package. It includes public objects that are
declared in package specification and objects that are to be used only within the package –
private members.
CREATE PACKAGE BODY package_name AS
/* define objects of package */
END;
procedures declared in the package specification
functions declared in the package specification
definition of cursors declared in the package specification
local procedures and functions, not declared in the package specification
local variables
Calling a procedure of package
In order to access a public object of a package use the following syntax:
package_name.object_name
package_name is the name of the package whose object you want to access.
 
object_name
Let us now create a package called COURSE_PKG that contains two subprograms –
CHANGEFEE and GETBATCHCOURSE.
We have to first create package specification and then body a follows:
is the name of a public object in the package.
create or replace package course_pkg as
procedure changefee (pccode varchar2, newfee number);
function getbatchcourse(pbcode varchar2) return varchar2;
end;
/
The following CREATE PACKAGE BODY statement creates the body of the package.
create or replace package body course_pkg as
procedure changefee (pccode varchar2, newfee number)
is
begin
update courses set fee = newfee
where ccode = pccode;
if sql%found then
commit;
else
raise_application_error(-20010,'Invalid course code');
end if;
end;
function getbatchcourse(pbcode varchar2) return varchar2
is
v_ccode courses.ccode%type;
begin
select ccode into v_ccode
from batches
where bcode = pbcode;
return v_ccode;
exception
when no_data_found then
raise_application_error( -20011,'Invalid batch code');
end;
end;
/

In order to call procedure CHANGEFEE of the package, use the package name followed by
procedure name as follows:
Execute course_pkg.changefee(‘ora’,5000);
Initializing package
It is possible to execute some code when a user refers the package for the first time. Any code
that you feel is to be executed at the time of a user referring to the package for the first time
can be put in initialization part of the package.
The following is the structure of initialization part.
create package body name as
/* definition of package body */
begin
/* code to be executed when package is referred for the first time */
end;
Summary
Stored procedure and functions are sub programs stored in database. Functions return a
single value whereas procedure doesn’t return any value. Stored procedures have important
advantages such as improving performance, making maintenance and security implementation
easy.
Oracle allows parameters of three types – in, out, and in out. OUT and IN OUT parameters
are used to return values back to calling program. They can be passed by either value
(default) or by reference using NOCOPY hint.
Procedures are executed under privileges of owner of the procedure. However, it is possible to
execute procedure with the privileges of invoker using AUTHID CURRENT_USER option of
CREATE PROCEDURE command.
Standard procedure RAISE_APPLICATION_ERROR is used to raise an application error with the
given error number and message.
 
Package allows a group of related procedures and function to be identified by a single name.
It is used to avoid name conflicts between names of different procedures.
Exercises
1. _____ command is used to display errors that occurred during compilation of a stored
procedure.
2. _______ view provides information about stored procedures.
3. _______option is used to specify that a parameter is both input and output parameter.
4. What is the command used to compile a procedure explicitly?
5. Create a procedure to insert a new row into payments with the given roll number. DP is
system date and AMOUNT is the amount to be paid by the student.
6. Create a function to take batch code and return the number of students in the batch.
7. Create a function to return the first missing roll number. If no roll number is missing then
return the highest roll number + 1.
8. Create a function to take faculty code and return the number of batches the faculty can
handle.
9. Create a procedure to take course code and return minimum and maximum duration of
batches of that course.
10. Create a package to contain the following procedures and functions.
Function BATCHSTATUS – takes batch code and returns S - if batch is yet to start, C – if
batch is completed, R – if batch is currently running.
Function BATCHAMOUNT – return the total amount collected from the given batch code.


================================================================================

Saturday, 7 July 2012

TRIGGERS

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.
 
------------
 
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>
--------------------
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.
-------------------------