PL/SQL After/Before Insert Trigger

• jay565260

  SSC Rookie

  Points: 34

  More actions

  March 28, 2008 at 9:38 am

  #180438

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

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  August 9, 2008 at 12:05 pm

  #855708

  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.