Home » SQL & PL/SQL » SQL & PL/SQL » After/Before Insert Trigger (SQL Plus 9i)
After/Before Insert Trigger [message #309759] Fri, 28 March 2008 12:31 Go to next message
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

Report message to a moderator

Re: After/Before Insert Trigger [message #309764 is a reply to message #309759] Fri, 28 March 2008 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you want to do but complex logic MUST be executed by procedures and NOT, NEVER by triggers.

In addition, '27-Mar-08' is NOT a date but a STRING.

Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

This time I did it for you, next time do it yourself.

Always post your Oracle version (4 decimals).

Regards
Michel

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

Report message to a moderator

Re: After/Before Insert Trigger [message #309864 is a reply to message #309764] Sat, 29 March 2008 07:39 Go to previous messageGo to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
What i want the trigger to do is, count the number of behaviors inserted into the behaviour table for a particular student, class and date.

IF the student receives 2 behaviours, generate a msg stating Lunch time detention. Currently the code generates this msg on the insert after i.e.

INSERT INTO behaviour values (13, 'Equipment', 5, 3, '27-Mar-08');
INSERT INTO behaviour values (14, 'Equipment', 5, 3, '27-Mar-08');
[b](I want the msg to appear after this insert because student 5 has received to behaviours)[/b]
INSERT INTO behaviour values (15, behaviour, 1, 4, '27-Mar-08');
[b]Lunch Time Detention[/b]

I have tried using both Before and After triggers. Any suggestions would be great thanks.
Re: After/Before Insert Trigger [message #309873 is a reply to message #309764] Sat, 29 March 2008 09:23 Go to previous messageGo to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
I have taken your good advice to turn the trigger function into a procedure. The procedure works when you execute it on its own however iam now getting the table is mutating error when calling it from the trigger. Can you suggest any work arounds im completly stuck.

Thanks

CREATE OR REPLACE PROCEDURE school_det_sp
  (s_id IN behaviour.be_stu_id%TYPE,
   c_id IN behaviour.be_class_id%TYPE,
   d_id IN behaviour.date_recieved%TYPE)
 IS
  lv_beh_total NUMBER (8);
  lv_punish VARCHAR2(40);
BEGIN
SELECT COUNT(behaviour_id)
   INTO lv_beh_total
   FROM behaviour 
   WHERE be_stu_id =s_id
   AND  be_class_id = c_id
   AND  date_recieved =d_id;
IF lv_beh_total = 2 THEN
	lv_punish:='Lunch Time Detension';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
--some kind of insert into detension detail
ELSIF lv_beh_total = 3 THEN
 	lv_punish:='After School Detension';
	DBMS_OUTPUT.PUT_LINE(lv_punish);
--some kind of insert into detension detail
		
END IF;
END;
/

EXECUTE school_det_sp (5,3,'29-MAR-08');

--Call the procedure from a trigger
CREATE OR REPLACE TRIGGER school_det_trg
AFTER INSERT ON behaviour
FOR EACH ROW
BEGIN
school_det_sp(:NEW.be_stu_id, :NEW.be_class_id, :NEW.date_recieved);
END;
/

[Updated on: Sat, 29 March 2008 09:25]

Report message to a moderator

Re: After/Before Insert Trigger [message #309874 is a reply to message #309759] Sat, 29 March 2008 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
If both GOOGLE & the search function of this forum are broken to the point you can't find any reference to your error, then you should get a refund from your ISP.
Re: After/Before Insert Trigger [message #309880 is a reply to message #309874] Sat, 29 March 2008 10:37 Go to previous messageGo to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
Are you being rude on purpose? i am new to this site ok, i have searched around this forum and on google for the last few days and i am struggling with the issue stated and require some guidance. If you don't want to help or have any positive suggestions, please keep your patronising comments to yourself.
Re: After/Before Insert Trigger [message #309881 is a reply to message #309873] Sat, 29 March 2008 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
complex logic MUST be executed by procedures and NOT, NEVER by triggers.

Remove trigger.
No more use direct insert but insert through the procedure that implements your function.
I repeat, there is no more trigger. There is no more direct insert.

Regards
Michel
Re: After/Before Insert Trigger [message #309885 is a reply to message #309881] Sat, 29 March 2008 10:53 Go to previous messageGo to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
Im confused Sad if i remove the trigger how will the procedure be "triggered" after an Insert to the table has occurred? I havent been using pl/sql that long so my knowledge is basic hence bein in the Newbies.

Thanks
Re: After/Before Insert Trigger [message #309889 is a reply to message #309885] Sat, 29 March 2008 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
There is no more direct insert.

You NEVER execute an INSERT, the only inserts allowed are from your procedure.

Regards
Michel

[Updated on: Sat, 29 March 2008 11:03]

Report message to a moderator

Re: After/Before Insert Trigger [message #309890 is a reply to message #309889] Sat, 29 March 2008 11:26 Go to previous messageGo to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
Thankyou very much for your help.
Re: After/Before Insert Trigger [message #309891 is a reply to message #309890] Sat, 29 March 2008 12:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
There are different philosophies on this subject. Some advocate control through procedures only without triggers and no other access such as SQL*Plus. However, if anybody, even a developer or DBA, inserts through SQL*Plus instead of a through a procedure, and I have seen this happen too many times, then you have a mess. So, I tend to prefer to use triggers. I might maintain the count in a separate table as shown below.

SCOTT@orcl_11g> CREATE TABLE behaviour
  2    (behaviour_id	NUMBER,
  3  	be_stu_id	NUMBER,
  4  	be_class_id	NUMBER,
  5  	date_received	DATE)
  6  /

Table created.

SCOTT@orcl_11g> CREATE TABLE behav_count
  2    (be_stu_id	NUMBER,
  3  	be_class_id	NUMBER,
  4  	date_received	DATE,
  5  	beh_total	NUMBER)
  6  /

Table created.

SCOTT@orcl_11g> CREATE TABLE detention
  2    (be_stu_id	NUMBER,
  3  	be_class_id	NUMBER,
  4  	date_received	DATE,
  5  	behaviors	NUMBER,
  6  	punishment	VARCHAR2 (25))
  7  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER behaviour_air
  2    AFTER INSERT ON behaviour
  3    FOR EACH ROW
  4  DECLARE
  5    v_count	NUMBER;
  6  BEGIN
  7    SELECT bc.beh_total + 1
  8    INTO   v_count
  9    FROM   behav_count bc
 10    WHERE  bc.be_stu_id = :NEW.be_stu_id
 11    AND    bc.be_class_id = :NEW.be_class_id
 12    AND    bc.date_received = :NEW.date_received;
 13    IF v_count = 2 THEN
 14  	 UPDATE behav_count bc
 15  	 SET	bc.beh_total = beh_total + 1
 16  	 WHERE	bc.be_stu_id = :NEW.be_stu_id
 17  	 AND	bc.be_class_id = :NEW.be_class_id
 18  	 AND	bc.date_received = :NEW.date_received;
 19  	 DBMS_OUTPUT.PUT_LINE (:NEW.be_stu_id || ' has ' || v_count || ' behaviors - lunch detention');
 20  	 INSERT INTO detention (be_stu_id, be_class_id, date_received, behaviors, punishment)
 21  	 VALUES (:NEW.be_stu_id, :NEW.be_class_id, :NEW.date_received, v_count, 'lunch detention');
 22    ELSIF v_count = 3 THEN
 23  	 UPDATE behav_count bc
 24  	 SET	bc.beh_total = beh_total + 1
 25  	 WHERE	bc.be_stu_id = :NEW.be_stu_id
 26  	 AND	bc.be_class_id = :NEW.be_class_id
 27  	 AND	bc.date_received = :NEW.date_received;
 28  	 DBMS_OUTPUT.PUT_LINE (:NEW.be_stu_id || ' has ' || v_count || ' behaviors - after school detention');
 29  	 INSERT INTO detention (be_stu_id, be_class_id, date_received, behaviors, punishment)
 30  	 VALUES (:NEW.be_stu_id, :NEW.be_class_id, :NEW.date_received, v_count, 'after school detention');
 31    END IF;
 32  EXCEPTION
 33    WHEN NO_DATA_FOUND THEN
 34  	 INSERT INTO behav_count VALUES
 35  	   (:NEW.be_stu_id, :NEW.be_class_id, :NEW.date_received, 1);
 36  END behaviour_air;
 37  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> INSERT INTO behaviour values (11, 1, 2, TRUNC (SYSDATE))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO behaviour values (12, 2, 4, TRUNC (SYSDATE))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO behaviour values (13, 5, 3, TRUNC (SYSDATE))
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO behaviour values (14, 5, 3, TRUNC (SYSDATE))
  2  /
5 has 2 behaviors - lunch detention

1 row created.

SCOTT@orcl_11g> INSERT INTO behaviour values (15, 1, 2, TRUNC (SYSDATE))
  2  /
1 has 2 behaviors - lunch detention

1 row created.

SCOTT@orcl_11g> INSERT INTO behaviour values (16, 5, 3, TRUNC (SYSDATE))
  2  /
5 has 3 behaviors - after school detention

1 row created.

SCOTT@orcl_11g> SELECT * FROM behaviour
  2  /

BEHAVIOUR_ID  BE_STU_ID BE_CLASS_ID DATE_RECE
------------ ---------- ----------- ---------
          11          1           2 29-MAR-08
          12          2           4 29-MAR-08
          13          5           3 29-MAR-08
          14          5           3 29-MAR-08
          15          1           2 29-MAR-08
          16          5           3 29-MAR-08

6 rows selected.

SCOTT@orcl_11g> SELECT * FROM behav_count
  2  /

 BE_STU_ID BE_CLASS_ID DATE_RECE  BEH_TOTAL
---------- ----------- --------- ----------
         1           2 29-MAR-08          2
         2           4 29-MAR-08          1
         5           3 29-MAR-08          3

SCOTT@orcl_11g> SELECT * FROM detention
  2  /

 BE_STU_ID BE_CLASS_ID DATE_RECE  BEHAVIORS PUNISHMENT
---------- ----------- --------- ---------- -------------------------
         5           3 29-MAR-08          2 lunch detention
         1           2 29-MAR-08          2 lunch detention
         5           3 29-MAR-08          3 after school detention

SCOTT@orcl_11g> 


Re: After/Before Insert Trigger [message #309893 is a reply to message #309891] Sat, 29 March 2008 12:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
if anybody, even a developer or DBA, inserts through SQL*Plus instead of a through a procedure, and I have seen this happen too many times, then you have a mess

No, as you don't allow this, for instance with a trigger ( Smile ) that checks an environment variable (package/context value, application info...).

Triggers are not for complex logic.
I think as a bad thing when something happens by magic like triggers do. More most of the time triggers are misused and only work in a single user environment.

As I said I didn't analyze what OP wants to do (actually I don't understand it) but anyway seeing your code I can say that:
- what happens if 2 users insert the same record at the same time? Then your "select count" is wrong (functionnaly but not for the database) and you insert twice "lunch detention" (or the like, as I said I don't really understand the process).
And I don't speak about the case when there are concurrent deletes and updates.

Regards
Michel

[Updated on: Sat, 29 March 2008 12:18]

Report message to a moderator

Re: After/Before Insert Trigger [message #309910 is a reply to message #309893] Sat, 29 March 2008 14:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
You could use one of Tom Kyte's methods with multiple triggers and package and such and some locking to use the trigger method in a multi-user environment, but I suppose the procedure method would be a lot simpler. If you commit within the procedure, which is something that is usually avoided but you can't have it both ways, then you can force a commit of one row at a time, so you get the count right. So, here is an example of the procedure method.

SCOTT@orcl_11g> CREATE TABLE behaviour
  2    (behaviour_id	NUMBER,
  3  	be_stu_id	NUMBER,
  4  	be_class_id	NUMBER,
  5  	date_received	DATE)
  6  /

Table created.

SCOTT@orcl_11g> CREATE TABLE detention
  2    (be_stu_id	NUMBER,
  3  	be_class_id	NUMBER,
  4  	date_received	DATE,
  5  	behaviours	NUMBER,
  6  	punishment	VARCHAR2 (25))
  7  /

Table created.

SCOTT@orcl_11g> CREATE SEQUENCE behaviour_id_seq
  2  /

Sequence created.

SCOTT@orcl_11g> CREATE OR REPLACE PROCEDURE insert_behaviour
  2    (p_stu_id   IN behaviour.be_stu_id%TYPE,
  3  	p_class_id IN behaviour.be_class_id%TYPE,
  4  	p_date	   IN behaviour.date_received%TYPE)
  5  AS
  6    v_count	      NUMBER;
  7  BEGIN
  8    SELECT COUNT (*) + 1
  9    INTO   v_count
 10    FROM   behaviour
 11    WHERE  be_stu_id = p_stu_id
 12    AND    be_class_id = p_class_id
 13    AND    date_received = p_date;
 14    IF v_count = 2 THEN
 15  	 DBMS_OUTPUT.PUT_LINE (p_stu_id || ' has ' || v_count || ' behaviours - lunch detention');
 16  	 INSERT INTO detention (be_stu_id, be_class_id, date_received, behaviours, punishment)
 17  	 VALUES (p_stu_id, p_class_id, p_date, v_count, 'lunch detention');
 18    ELSIF v_count = 3 THEN
 19  	 DBMS_OUTPUT.PUT_LINE (p_stu_id || ' has ' || v_count || ' behaviours - after school detention');
 20  	 INSERT INTO detention (be_stu_id, be_class_id, date_received, behaviours, punishment)
 21  	 VALUES (p_stu_id, p_class_id, p_date, v_count, 'after school detention');
 22    END IF;
 23    INSERT INTO behaviour (behaviour_id, be_stu_id, be_class_id, date_received)
 24    SELECT behaviour_id_seq.NEXTVAL, p_stu_id, p_class_id, p_date FROM DUAL;
 25    COMMIT;
 26  END insert_behaviour;
 27  /

Procedure created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> exec insert_behaviour (1, 2, TRUNC (SYSDATE))

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec insert_behaviour (2, 4, TRUNC (SYSDATE))

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec insert_behaviour (5, 3, TRUNC (SYSDATE))

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec insert_behaviour (5, 3, TRUNC (SYSDATE))
5 has 2 behaviours - lunch detention

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec insert_behaviour (1, 2, TRUNC (SYSDATE))
1 has 2 behaviours - lunch detention

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> exec insert_behaviour (5, 3, TRUNC (SYSDATE))
5 has 3 behaviours - after school detention

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT * FROM behaviour
  2  /

BEHAVIOUR_ID  BE_STU_ID BE_CLASS_ID DATE_RECE
------------ ---------- ----------- ---------
           1          1           2 29-MAR-08
           2          2           4 29-MAR-08
           3          5           3 29-MAR-08
           4          5           3 29-MAR-08
           5          1           2 29-MAR-08
           6          5           3 29-MAR-08

6 rows selected.

SCOTT@orcl_11g> SELECT * FROM detention
  2  /

 BE_STU_ID BE_CLASS_ID DATE_RECE BEHAVIOURS PUNISHMENT
---------- ----------- --------- ---------- -------------------------
         5           3 29-MAR-08          2 lunch detention
         1           2 29-MAR-08          2 lunch detention
         5           3 29-MAR-08          3 after school detention

SCOTT@orcl_11g> 

Re: After/Before Insert Trigger [message #309918 is a reply to message #309910] Sat, 29 March 2008 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As you work with multiple statements and multiple tables you either have to:
- lock the tables
- use serializable mode if you expect few concurrent processes

Regards
Michel
Re: After/Before Insert Trigger [message #309919 is a reply to message #309910] Sat, 29 March 2008 14:58 Go to previous messageGo to next message
jay565260
Messages: 9
Registered: March 2008
Junior Member
Thankyou for your assistance i realy appreciate it. With regards to using count is it possible to reset it? so that a student say who gets 6 behaviours in one day would receive a lunch time and an afterschool detention?

Kindest Regards

Jay
Re: After/Before Insert Trigger [message #309920 is a reply to message #309919] Sat, 29 March 2008 15:13 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
jay565260 wrote on Sat, 29 March 2008 12:58
Thankyou for your assistance i realy appreciate it. With regards to using count is it possible to reset it? so that a student say who gets 6 behaviours in one day would receive a lunch time and an afterschool detention?

Kindest Regards

Jay



I am not sure that I understand the requirement. The way that the procedure that I supplied works is that, after 2 behaviours in the same day, the student gets lunch detention, after a 3rd behaviour on the same day, the student gets detention after school, in addition to the lunch detention. Nothing happens after just 1 behaviour and nothing additional happens after 4 or more behaviours in the same day. If you want it differently, then just change the 2 and 3 to whatever numbers you want and add more routines for additional numbers. However, if you intended for the after school detention after the 3rd behaviour to be instead of the lunch detention after the second behaiour, then you will need to add some extra code to delete the lunch detention from the detention table.

If you still need help, then please provide more explanation and a copy and paste of what you tried and the results.

Previous Topic: What is bind variable?where its stored?
Next Topic: How to delete duplicate rows?
Goto Forum:
  


Current Time: Wed Dec 07 18:52:18 CST 2016

Total time taken to generate the page: 0.08667 seconds