March 28, 2008 at 9:38 am
Hi
iam trying to generate an after insert trigger for a student monitoring system which counts the number of "behavoiurs" for a particular student and places the value in a packaged variable. Then the variable is placed into an IF statement, if the student recieves 2 "behaviours" Lunch time detention 3 "behaviours" After school detention. My problem is the triggers flags up a detention on the row after, i.e 2 "behaviours" inserted-No message flag, on the next insert-Lunch Time Detention msg appears. Is there a way round this ive tried using both Before and After Insert Triggers?
Until i can get round this i cant move forward and ive been trying for 3 days,would really appreciate any assistance.
Thanks
Jay
--Convert to a packaged procedure
CREATE OR REPLACE PACKAGE behav_pkg AS
pv_beh_total NUMBER (8);
pv_date DATE;
pv_stu NUMBER (10);
pv_class NUMBER (10);
pv_punish VARCHAR2(40);
END;
/
--Create statement level trigger
--DROP TRIGGER check_det_trg;
CREATE OR REPLACE TRIGGER check_det_trg
AFTER INSERT ON behaviour
BEGIN
SELECT COUNT(behaviour_id)
INTO behav_pkg.pv_beh_total
FROM behaviour
WHERE be_stu_id =behav_pkg.pv_stu
AND be_class_id = behav_pkg.pv_class
AND date_recieved =behav_pkg.pv_date;
END;
/
--Create row level trigger
--DROP TRIGGER check_beh_trg;
CREATE OR REPLACE TRIGGER check_beh_trg
BEFORE INSERT ON behaviour
FOR EACH ROW
BEGIN
IF behav_pkg.pv_beh_total = 2 THEN
behav_pkg.pv_punish:='Lunch Time Detension';
DBMS_OUTPUT.PUT_LINE(behav_pkg.pv_punish);
--some kind of insert into detension detail
ELSIF behav_pkg.pv_beh_total = 3 THEN
behav_pkg.pv_punish:='After School Detension';
DBMS_OUTPUT.PUT_LINE(behav_pkg.pv_punish);
behav_pkg.pv_beh_total:=0;
behav_pkg.pv_stu:=0;
behav_pkg.pv_class:=0;
DBMS_OUTPUT.PUT_LINE(behav_pkg.pv_stu);
DBMS_OUTPUT.PUT_LINE(behav_pkg.pv_class);
DBMS_OUTPUT.PUT_LINE(behav_pkg.pv_beh_total);
--some kind of insert into detension detail
END IF;
END;
/
--Test Trigger firstly initialize statement level trigger with the student and class id
BEGIN
behav_pkg.pv_stu:=5;
END;
/
BEGIN
behav_pkg.pv_class:=3;
END;
/
BEGIN
behav_pkg.pv_date:='27-Mar-08';
END;
/
INSERT INTO behaviour values (11, 'Equipment', 1, 2, '27-Mar-08');
INSERT INTO behaviour values (12, 'Equipment', 2, 4, '27-Mar-08');
INSERT INTO behaviour values (13, 'Equipment', 5, 3, '27-Mar-08');
INSERT INTO behaviour values (14, 'Equipment', 5, 3, '27-Mar-08');
INSERT INTO behaviour values (15, 'Behaviour', 5, 3, '27-Mar-08');
INSERT INTO behaviour values (16, 'Equipment', 5, 3, '27-Mar-08');
August 9, 2008 at 12:05 pm
This is a text book example of what you may expect to happen when people tries to pack the application's logic into triggers.
Application's logic should be on the application side.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply