OraFAQ Forum: Training & Certification » Triggerzzzz (like a tiger)

Home » Other » Training & Certification » Triggerzzzz (like a tiger) (PL/SQL 9i)

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

Triggerzzzz (like a tiger) [message #364283]

Sun, 07 December 2008 18:56

lolly
Messages: 5
Registered: December 2008
Location: Montreal

Junior Member

Hello everyone! I am very happy to see there are lots of pple here!! I am actually trying to build a trigger, but it's invalid.
Im 99% sure about the select part, seems like I got a problem with some ;
Sad

here is what I did up to now, if anybody could help me, this would be really nice! thanks

------------------------------
CREATE TRIGGER TRIGVENTES
BEFORE UPDATE OR INSERT ON VENTE
FOR EACH ROW
DECLARE
resultat number;
BEGIN
SELECT COUNT(*) INTO resultat
FROM VENTE V, CROISIERE CR, VOYAGE VO, NAVIRE N, CABINE CA
WHERE V.CODE_CROISIERE = CR.CODE_CROISIERE
AND CR.CODE_VOYAGE = VO.CODE_VOYAGE
AND VO.NO_NAVIRE = N.NO_NAVIRE
AND N.NO_NAVIRE = CA.NO_NAVIRE
AND V.CODE_CROISIERE = :NEW.CODE_CROISIERE
AND V.NO_CABINE = :NEW.NO_CABINE;
IF (resultat := 0)
THEN raise_application_error(-20001, 'NO_CABINE APPARTIENT PAS AU NAVIRE');
END IF;
END ;

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364288 is a reply to message #364283]

Sun, 07 December 2008 20:07

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Since you decided not to tell us what the error was, I'll use my super-spidey-senses to work it out for myself.

HHHHHHHHHMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM??????????

Got it! You got a Mutating Table error, right?

A FOR EACH ROW trigger cannot access the table that it is firing on. Take VENTE out of the SQL and replace the V.<> columns in the SQL with :NEW.<>

Ross Leishman

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364289 is a reply to message #364283]

Sun, 07 December 2008 20:10

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

:= is used for assigning values and = is used for comparing values, so "if (resultat := 0)" should be "if resultat = 0". That may allow it to compile, but you will still have a problem using it, because you are selecting from the same table that the trigger is on from within the trigger, so you are likely to get a mutating error. If you can select from the other tables without selecting from the vente table that might work. Perhaps if you could describe what you are trying to accomplish, it would make it easier to help. Please read our forum guideline for suggestions on how to post questions.

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364291 is a reply to message #364289]

Sun, 07 December 2008 20:27

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

My answer would have been funnier if it had been right. Mad

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364292 is a reply to message #364289]

Sun, 07 December 2008 20:37

lolly
Messages: 5
Registered: December 2008
Location: Montreal

Junior Member

hi there again, thanks for replying.
I read the rules, but still looking to see how i can post in SQLplus version. I'm using aqua data studio, if anybody knows...

in fact, this trigger is <supposed> to check if somebody wants to update or insert in vente) in the "cabine" table if there is a NO_CABINE associated with the particular NO_NAVIRE
there is the DSD:

http://armstrong.hec.ca:8080/275004/A01/etud0051/untitled.JPG

and (sorry for the format) these are the errors produced (I have already changed deht := for = in the If statement)
:
>[Error] Script lines: 8-16 -------------------------
ORA-06550: line 9, column 36:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;

[Executed: 08-12-07 21:23:05 EST ] [Execution: 0/ms]

>[Error] Script lines: 17-18 ------------------------
ORA-00900: invalid SQL statement

[Executed: 08-12-07 21:23:05 EST ] [Execution: 0/ms]

>[Error] Script lines: 19-19 ------------------------
ORA-00900: invalid SQL statement

[Executed: 08-12-07 21:23:05 EST ] [Execution: 0/ms]

>[Error] Script lines: 20-20 ------------------------
ORA-00900: invalid SQL statement

[Executed: 08-12-07 21:23:05 EST ] [Execution: 0/ms]

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364294 is a reply to message #364292]

Sun, 07 December 2008 20:59

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Like I said you need to not select from the vente table. To do that, you can do like Ross said and remove vente from the select statement and replace each "v." with ":new." as shown below. There seems to be some conflict in between your diagram and code as to the underscores in the column names, so check those.

SELECT COUNT(*) 
INTO   resultat
FROM   CROISIERE CR, VOYAGE VO, NAVIRE N, CABINE CA
WHERE  :NEW.CODE_CROISIERE = CR.CODE_CROISIERE
AND    CR.CODE_VOYAGE = VO.CODE_VOYAGE
AND    VO.NO_NAVIRE = N.NO_NAVIRE
AND    N.NO_NAVIRE = CA.NO_NAVIRE
AND    CA.NO_CABINE = :NEW.NO_CABINE;

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364295 is a reply to message #364294]

Sun, 07 December 2008 21:09

lolly
Messages: 5
Registered: December 2008
Location: Montreal

Junior Member

Oh my thanks so much for the tip (I didn't know I couldn't use the modified table in the select)

but still I've got the same errors :'(

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364296 is a reply to message #364295]

Sun, 07 December 2008 21:13

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Do you get the error when you try to compile the trigger or when you try to do an insert or update or delete? Did you check whether all of the underscores in the column names belong there or not? It is really hard to guess what is wrong without seeing a copy and paste of compilation and error message with line numbers from SQL*Plus and create table statements. At least copy and paste your current complete trigger code. It could be something as simple as a missing punctuation mark somewhere.

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364297 is a reply to message #364296]

Sun, 07 December 2008 21:31

lolly
Messages: 5
Registered: December 2008
Location: Montreal

Junior Member

http://armstrong.hec.ca:8080/275004/A01/etud0051/untitled2.JPG

errors:

0 record(s) affected

[Executed: 08-12-07 22:26:41 EST ] [Execution: 32/ms]

Warnings: --->
W (1): Warning: ex�cution termin�e avec avertissement
<---

0 record(s) affected

[Executed: 08-12-07 22:26:41 EST ] [Execution: 31/ms]

>[Error] Script lines: 7-14 -------------------------
ORA-06550: line 8, column 40:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;

[Executed: 08-12-07 22:26:41 EST ] [Execution: 0/ms]

>[Error] Script lines: 15-16 ------------------------
ORA-00900: invalid SQL statement

[Executed: 08-12-07 22:26:41 EST ] [Execution: 0/ms]

>[Error] Script lines: 17-17 ------------------------
ORA-00900: invalid SQL statement

[Executed: 08-12-07 22:26:41 EST ] [Execution: 0/ms]

>[Error] Script lines: 18-18 ------------------------
ORA-00900: invalid SQL statement

[Executed: 08-12-07 22:26:42 EST ] [Execution: 0/ms]

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364298 is a reply to message #364297]

Sun, 07 December 2008 21:43

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

I am repeating myself here, because you have not provided answers. Do you get these errors when you compile the trigger or when you try to insert or update or delete? Did you check the underscores? Can you provide create table statements or at least do a describe to list the table structure? If the errors come from an insert, then provide the insert.

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364300 is a reply to message #364297]

Sun, 07 December 2008 21:57

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Here is an example for you, using the emp and dept demo tables, showing descriptions of the tables, compilation of the trigger, test of valid insert, and test of invalid insert, all from SQL*Plus. Perhaps you can compare this to what you are trying to do.

SCOTT@orcl_11g> DESCRIBE emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SCOTT@orcl_11g> DESCRIBE dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SCOTT@orcl_11g> CREATE OR REPLACE TRIGGER example_trigger
  2    BEFORE UPDATE OR INSERT ON emp
  3    FOR EACH ROW
  4  DECLARE
  5    resultat NUMBER;
  6  BEGIN
  7    SELECT COUNT(*)
  8    INTO   resultat
  9    FROM   dept d
 10    WHERE  d.deptno = :NEW.deptno;
 11    --
 12    IF resultat = 0
 13  	 THEN raise_application_error(-20001, 'NO SUCH DEPARTMENT');
 14    END IF;
 15  END example_trigger;
 16  /

Trigger created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- test of valid insert:
SCOTT@orcl_11g> INSERT INTO emp (deptno, empno) VALUES (40, 99)
  2  /

1 row created.

SCOTT@orcl_11g> -- test of invalid insert:
SCOTT@orcl_11g> INSERT INTO emp (deptno, empno) VALUES (50, 88)
  2  /
INSERT INTO emp (deptno, empno) VALUES (50, 88)
            *
ERROR at line 1:
ORA-20001: NO SUCH DEPARTMENT
ORA-06512: at "SCOTT.EXAMPLE_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SCOTT.EXAMPLE_TRIGGER'


SCOTT@orcl_11g>

Report message to a moderator

Re: Triggerzzzz (like a tiger) [message #364302 is a reply to message #364300]

Sun, 07 December 2008 22:18

lolly
Messages: 5
Registered: December 2008
Location: Montreal

Junior Member

Oh mmmmmy. I've been on this for 12 hours. compiler was retarded: I hate to alter trigger and save there, and it worked #1.

I have to tank all of you for your help
and for info, this was while creating trigger!
it was stopping at every ";"

joyful joyful loooord Very Happy

Report message to a moderator

Previous Topic:	Need Help On Pl/sql ( Very very Challenging)
Next Topic:	Need Help with an Expression

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 27 22:41:41 CST 2024