Table Name: INSTRUCTOR
INSTRUCTOR_ID INSTRUCTOR_NAME SALARY COMMISSION MENTOR_ID DATE_HIRE
------------- --------------- --------- ---------- --------- ---------
700 WAYNE 4500 300 16-MAY-81
628 MONROE 3000 700 16-JUN-84
790 NEWMAN 3100 300 700 16-DEC-82
795 BOGART 3200 700 16-DEC-83
515 SHELLEY 3500 200 700 20-JAN-91
222 CAINE 5500 350 02-NOV-76
978 STEEL 5000 250 222 16-JAN-80
560 LAUREL 5200 200 978 16-DEC-83
835 SPARKS 4000 200 978 16-DEC-84
243 TUCKER 2000 835 18-DEC-90
263 JOHNSON 4000 835 18-JUL-92
453 LODGE 2500 100 835 14-SEP-88
Table Name: AUTH
INSTRUCTOR_ID COURSE_ID AUTH_DATE
------------- --------- ---------
222 315 14-MAR-90
222 415 13-JAN-92
978 815 14-MAR-89
700 315 14-FEB-91
835 515 12-MAR-92
515 915 22-APR-91
263 215 14-JUN-92
263 315 14-FEB-92
453 715 14-AUG-93
790 315 14-AUG-91
560 915 14-AUG-91
453 615 14-JUL-93
628 315 13-JUL-91
My Question requirement is:
1) From the INSTRUCTOR table, if the salary of that instructor >= 3750, then he/she can insert new record on the AUTH table, BUT if salary <>
SO, I SOLVE this question using the method below, and can some one help me to check any logic error on it.
cause even i insert a new record where the instructor salary already <>
******************************************************
INSERT INTO INSTRUCTOR(INSTRUCTOR_ID,
INSTRUCTOR_NAME,
SALARY,
COMMISSION,
MENTOR_ID,
DATE_HIRED)
VALUES (100, 'JOSH', 2000, 500, 700, '11-AUG-05');
INSERT INTO AUTH(INSTRUCTOR_ID,
COURSE_ID,
AUTH_DATE)
VALUES(100, 515, '11-AUG-05');
******************************************************
=============================================================================
CREATE OR REPLACE TRIGGER CHECK_SALARY_TR
BEFORE INSERT
ON AUTH
DECLARE
SALARY INSTRUCTOR.SALARY%TYPE;
BEGIN
IF (SALARY >= 3750)THEN
RAISE_APPLICATION_ERROR(-20101,'THANKS, YOU HAVE AN AUTHORIZATION TO OFFER COURSES');
ELSIF (SALARY <>
RAISE_APPLICATION_ERROR(-20102,'SORRY, YOU DO NOT HAVE AUTHORIZATION TO OFFER COURSES');
END IF;
END;
=============================================================================
CREATE OR REPLACE PROCEDURE INSERT_AUTH_PROC(P_INSTRUCTOR_ID AUTH.INSTRUCTOR_ID%TYPE,
P_COURSE_ID AUTH.COURSE_ID%TYPE,
P_AUTH_DATE AUTH.AUTH_DATE%TYPE ) IS
BEGIN
INSERT INTO AUTH(INSTRUCTOR_ID, COURSE_ID, AUTH_DATE)
VALUES (P_INSTRUCTOR_ID, P_COURSE_ID, P_AUTH_DATE);
END INSERT_AUTH_PROC;
=============================================================================
--------------------------------------->
In your trigger you have defined a variable SALARY and used it in the IF..ELSIF. Since the value of SALARY is NULL (you haven't initialized it) both the IF and ELSIF will evaluate to FALSE. You need to populate the variable, probably by querying the INSTRUCTOR table with the ID of the instructor from the insert. Use the NEW record supplied by the trigger (:new.instructor_id).
Also, I don't think you want to use the RAISE_APPLICATION_ERROR if the insert is allowed. Doing so will cause the insert to fail. Perhaps you are just doing that for testing.
--------------------------------------->
=============================================================================
CREATE OR REPLACE TRIGGER CHECK_SALARY_TR
BEFORE INSERT
ON AUTH
DECLARE
SALARY INSTRUCTOR.SALARY%TYPE;
BEGIN
IF (SALARY < 3750) THEN
RAISE_APPLICATION_ERROR(-20101,'SORRY, YOU DO NOT HAVE AUTHORIZATION TO OFFER COURSES');
END IF;
END;
=============================================================================
CREATE OR REPLACE PROCEDURE INSERT_AUTH_PROC(P_INSTRUCTOR_ID AUTH.INSTRUCTOR_ID%TYPE,
P_COURSE_ID AUTH.COURSE_ID%TYPE,
P_AUTH_DATE AUTH.AUTH_DATE%TYPE ) IS
BEGIN
INSERT INTO AUTH(INSTRUCTOR_ID, COURSE_ID, AUTH_DATE)
VALUES (P_INSTRUCTOR_ID, P_COURSE_ID, P_AUTH_DATE);
END INSERT_AUTH_PROC;
=============================================================================
How to modify the trigger coding so that the SALARY can refer to the most currently inserted new row?
example: I insert new record into the INSTRUCTOR table where the new instructor have the SALARY <>
It can run smoothly if i change the ......
SALARY INSTRUCTOR.SALARY%TYPE;
TO BECOME
SALARY INSTRUCTOR.SALARY%TYPE:=2000; (this will not allow me to insert into AUTH table)
SALARY INSTRUCTOR.SALARY%TYPE:=5000; (this will allow me to insert into AUTH table)
BUT I don't want it to be fix value! I want the trigger to check the SALARY on the new instructor that i just inserted.
PROBLEM: I don't really know how to use the :new and :old like what you told me, if not mistaken, the :new should be only available on "BEFORE INSERT or update" right.
--------------------------------------->
You're correct, the NEW and OLD are only available to row triggers. Sorry about that, didn't catch that your trigger was not a row trigger. Could you make this a row trigger instead? Or, add the validation code to the procedure, prior to the insert.
--------------------------------------->
Thanks again for your help one more time. I try to implement the technique that you suggest and now I done perfectly. Thanks.
Below is the code that run successfully. with make sure of :NEW command
=============================================================================
CREATE OR REPLACE TRIGGER CHECK_SALARY_TR
BEFORE INSERT
ON AUTH
FOR EACH ROW
DECLARE
V_SALARY INSTRUCTOR.SALARY%TYPE;
BEGIN
SELECT SALARY
INTO V_SALARY
FROM INSTRUCTOR
WHERE INSTRUCTOR_ID = :NEW.INSTRUCTOR_ID;
IF (V_SALARY < 3750) THEN
RAISE_APPLICATION_ERROR(-20101,'SORRY, YOU DO NOT HAVE AUTHORIZATION TO OFFER COURSES');
END IF;
END;
RSS Feed (xml)
