March 28, 2008 at 5: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');
March 28, 2008 at 9:32 am
You're asking an Oracle question on a strictly SQL Server forum. You may get lucky and get help from someone here who works on both platforms, but you're more likely to find help on an Oracle website like http://www.orafaq.com/ or Oracle's Metalink site.
Good Luck
Greg
March 28, 2008 at 9:32 am
You would probably get better response if you moved this to one of the "Working with Oracle" forums on this site.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply