Using Trigger And Procedure
Together
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 < 3750, he/she can not insert a new
record with display error message. this question need the trigger and procedure
to solve the problem.
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 < 3750 but i still can insert a
new record into the AUTH table, why??........example...testing coding *********
******************************************************
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 < 3750) THEN
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 < 3750, then when he want to
insert himself into the AUTH table, he can't cause the SALARY < 3750
unless his salary change to more that 3750?
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;
=============================================================================
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle Certification,
Database Administration, SQL, Application, Programming Reference Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|