Incorrect results in Database Trigger.

Gurus,

I'm getting incorrect values when using a trigger. When I execute the query in the trigger, manually, I get the correct values.

Example:

SQL>   CREATE TABLE "CMKAT"."ART_TEST" (        "ARTKEYI" NUMBER, "ARTBEZC" VARH
AR2(30 CHAR),"ARTVF1C" VARCHAR2(30 CHAR));

Table created.

SQL> CREATE TABLE "CMKAT"."PRO_TEST" (  "PROKEYI" NUMBER, "PROBEZC" VARCHAR2(30
CHAR), "PROVF1C" VARCHAR2(30 CHAR)) ;

Table created.

SQL>   CREATE TABLE "CMKAT"."LOOKUP" (  "LOOKUP_ARTKEYI" NUMBER, "LOOKUP_PROKEYI
" NUMBER);

Table created.

SQL> Insert into ART_TEST (ARTKEYI,ARTBEZC,ARTVF1C) values (3001,'pants',null);

1 row created.

SQL> Insert into ART_TEST (ARTKEYI,ARTBEZC,ARTVF1C) values (3002,'socks',null);

1 row created.

SQL> Insert into PRO_TEST (PROKEYI,PROBEZC,PROVF1C) values (4001,'outfit',null);

1 row created.

SQL> Insert into LOOKUP(LOOKUP_ARTKEYI,LOOKUP_PROKEYI) values (3001,4001);

1 row created.

SQL> Insert into LOOKUP(LOOKUP_ARTKEYI,LOOKUP_PROKEYI) values (3002,4001);

1 row created.

SQL> commit;

Commit complete.

SQL> SELECT LISTAGG(ARTBEZC,',') WITHIN GROUP (
ORDER BY ARTKEYI) CON,
  PROBEZC GRP
FROM ART_TEST,
  PRO_TEST,
  LOOKUP
WHERE ART_TEST.ARTKEYI = LOOKUP.LOOKUP_ARTKEYI
AND PRO_TEST.PROKEYI   = LOOKUP.LOOKUP_PROKEYI
GROUP BY PROBEZC;

CON                    GRP
----------------        --------
pants,socks       outfit

Open in new window


Trigger:

CREATE OR REPLACE TRIGGER Trigg_Test before
  INSERT OR
  UPDATE ON ART_TEST FOR EACH ROW 
  DECLARE 
  ARTICLE_NAME VARCHAR2(100);
  ELEMENT_NAME VARCHAR2(100);
  BEGIN
    SELECT LISTAGG(:NEW.ARTBEZC,',') WITHIN GROUP (
    ORDER BY :new.ARTKEYI) CON,
      PROBEZC GRP
    INTO article_name,
      element_name
    FROM
      PRO_TEST,
      LOOKUP
    WHERE :new.ARTKEYI = LOOKUP.LOOKUP_ARTKEYI
    AND PRO_TEST.PROKEYI   = LOOKUP.LOOKUP_PROKEYI
    GROUP BY PROBEZC;
    INSERT INTO TEST_TABLE_FOR_TRIGGER VALUES
      (ARTICLE_NAME,ELEMENT_NAME);
  END;

Open in new window


Test Trigger (Results):

SQL> UPDATE ART_TEST SET ARTVF1C = 'TEST'
where artkeyi in (3001,3002);

2 rows updated.

SQL> commit;

Commit complete.

SQL> SELECT * FROM TEST_TABLE_FOR_TRIGGER;

ART_CONCAT     ELEMENT
-------                     -------  
pants                   outfit
socks		     outfit

Open in new window


Results should be:

ART_CONCAT     ELEMENT
-------                     -------  
pants,socks        outfit


Why am I getting different results when the trigger is executed and not the same results as the query?
xbox360dpAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
why would you expect them to be the same?


In the first query you are pulling all of the ARTKEYI values from the ART_TEST table.

In the trigger query you are pulling a single value from one row of the ART_TEST table.
0
 
johnsoneSenior Oracle DBACommented:
Isn't this a duplicate of:

http://www.experts-exchange.com/Database/Oracle/Q_28614994.html

You were asked for more information there and it never was posted.  Why not just provide the additional information in the original question rather than opening a new question.
0
 
xbox360dpAuthor Commented:
Sbstuber,

What do I need to do to change it?
0
 
xbox360dpAuthor Commented:
Sorry ... and get the results I'm after?
0
 
sdstuberCommented:
you can't do it inside a row level trigger because the trigger can only see its own row.


you could try a statement level trigger using the original query (i.e. don't reference :NEW)  but then you'll only get one row inserted for each insert or update statement,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.