OraFAQ Forum: SQL & PL/SQL » After/Before Insert Trigger

Home » SQL & PL/SQL » SQL & PL/SQL » After/Before Insert Trigger (SQL Plus 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

After/Before Insert Trigger [message #309759]

Fri, 28 March 2008 12:31

jay565260
Messages: 9
Registered: March 2008

Junior Member

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 trigger 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 so that on the 2nd insert of a "behaviour" u get the lunchtime detention msg, and likewise for 3 "behaviours" ive tried using both Before and After Insert Triggers and still no luck.

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');

[Updated on: Fri, 28 March 2008 13:27] by Moderator