SQL CODE:
SQL> DESC colstu;
Name Null? Type
------------------------------- -------- ----
IDNO NUMBER(4)
NAME VARCHAR2(20)
NUMCR NUMBER(3)
CLASSLEV VARCHAR2(8)
SQL> desc credit_range;
Name Null? Type
------------------------------- -------- ----
YEAR_NAME NOT NULL VARCHAR2(8)
MINCREDITS NUMBER(3)
MAXCREDITS NUMBER(3)
SQL> SELECT * FROM credit_range;
YEAR_NAM MINCREDITS MAXCREDITS
-------- ---------- ----------
FRESHMAN 0 30
SOPHMORE 31 60
JUNIOR 61 90
SENIOR 91 999
The PL/SQL program and the trigger associated with putting records on
the colstu table are shown below.
SQL CODE:
SQL> edit use_stu_trig
PL/SQL CODE:
DECLARE
v_idno colstu.idno%TYPE := &in_idno;
v_name colstu.name%TYPE := '&in_name';
v_numcr colstu.numcr%TYPE := &in_numcr;
v_classlev colstu.classlev%TYPE := '&in_classlev';
BEGIN
INSERT INTO colstu
VALUES (v_idno, v_name, v_numcr, v_classlev);
END;
/
SQL CODE:
SQL> edit stu_trig2
PL/SQL CODE:
SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER student_log
BEFORE INSERT ON colstu
FOR EACH ROW
WHEN (new.classlev != 'SENIOR')
DECLARE
v_mincredits NUMBER;
v_maxcredits NUMBER;
BEGIN
SELECT mincredits, maxcredits into v_mincredits, v_maxcredits
FROM credit_range
WHERE year_name = :new.classlev;
IF :new.numcr < v_mincredits or :new.numcr > v_maxcredits THEN
RAISE_APPLICATION_ERROR (-20001, 'CREDITS OUT OF RANGE FOR '
|| :new.idno || ' ' || :new.name);
END IF;
END;
/
SET SERVEROUTPUT OFF
I called this trigger student_log and I said that it should be done
BEFORE insert on colstu. In this line you can also use AFTER. If you
are doing an update instead of an insert you can list columns in this
clause. This should be done for each row and the when clause
eliminates SENIOR from the checking.
Note the use of :old and :new in the row level trigger. These define
pseudo records that refer in the case of an update to the before and
after. With insert the :old is undefined and with delete the :new is
undefined. The colon is required and old and new are reserved words
with the meaning that accesses the before and after.
Notice that the :new is used in eliminating the senior and in the IF
where I want to compare the new record that has not been written with
the mincredits and maxcredits that came out of credit_range.
The RAISE_APPLICATION_ERROR is a built in function that allows the
programmer to create error messages appropriate for their applications.
The programmer can select a number between -20000 and -20999 and then
code the error message that they plan to use.
The next section shows a variety of inputs into the program. Note:
first I need to run execute the trigger to make sure it has been
created.
SQL CODE:
SQL> @ stu_trig2
Trigger created.
SQL> @ use_stu_trig
Input truncated to 1 characters
Enter value for in_idno: 1234
old 2: v_idno colstu.idno%TYPE := &in_idno;
new 2: v_idno colstu.idno%TYPE := 1234;
Enter value for in_name: John Doe
old 3: v_name colstu.name%TYPE := '&in_name';
new 3: v_name colstu.name%TYPE := 'John Doe';
Enter value for in_numcr: 15
old 4: v_numcr colstu.numcr%TYPE := &in_numcr;
new 4: v_numcr colstu.numcr%TYPE := 15;
Enter value for in_classlev: FRESHMAN
old 5: v_classlev colstu.classlev%TYPE := '&in_classlev';
new 5: v_classlev colstu.classlev%TYPE := 'FRESHMAN';
PL/SQL procedure successfully completed.
SQL> SELECT * FROM colstu;
IDNO NAME NUMCR CLASSLEV
--------- -------------------- --------- --------
1234 John Doe 15 FRESHMAN
SQL> @ use_stu_trig
Input truncated to 1 characters
Enter value for in_idno: 2345
old 2: v_idno colstu.idno%TYPE := &in_idno;
new 2: v_idno colstu.idno%TYPE := 2345;
Enter value for in_name: Jane Doe
old 3: v_name colstu.name%TYPE := '&in_name';
new 3: v_name colstu.name%TYPE := 'Jane Doe';
Enter value for in_numcr: 15
old 4: v_numcr colstu.numcr%TYPE := &in_numcr;
new 4: v_numcr colstu.numcr%TYPE := 15;
Enter value for in_classlev: JUNIOR
old 5: v_classlev colstu.classlev%TYPE := '&in_classlev';
new 5: v_classlev colstu.classlev%TYPE := 'JUNIOR';
DECLARE
*
ERROR at line 1:
ORA-20001: CREDITS OUT OF RANGE FOR 2345 Jane Doe
ORA-06512: at "SCOTT.STUDENT_LOG", line 9
ORA-04088: error during execution of trigger 'SCOTT.STUDENT_LOG'
ORA-06512: at line 7
SQL> SELECT * FROM colstu;
IDNO NAME NUMCR CLASSLEV
--------- -------------------- --------- --------
1234 John Doe 15 FRESHMAN
SQL> @ use_stu_trig
Input truncated to 1 characters
Enter value for in_idno: 3456
old 2: v_idno colstu.idno%TYPE := &in_idno;
new 2: v_idno colstu.idno%TYPE := 3456;
Enter value for in_name: Ann Smith
old 3: v_name colstu.name%TYPE := '&in_name';
new 3: v_name colstu.name%TYPE := 'Ann Smith';
Enter value for in_numcr: 60
old 4: v_numcr colstu.numcr%TYPE := &in_numcr;
new 4: v_numcr colstu.numcr%TYPE := 60;
Enter value for in_classlev: SENIOR
old 5: v_classlev colstu.classlev%TYPE := '&in_classlev';
new 5: v_classlev colstu.classlev%TYPE := 'SENIOR';
PL/SQL procedure successfully completed.
SQL> SELECT * FROM colstu;
IDNO NAME NUMCR CLASSLEV
--------- -------------------- --------- --------
3456 Ann Smith 60 SENIOR
1234 John Doe 15 FRESHMAN