Solved

Incorrect results in Database Trigger.

Posted on 2015-02-16
5
197 Views
Last Modified: 2015-02-17
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?
0
Comment
Question by:xbox360dp
  • 2
  • 2
5 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 40612892
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
 
LVL 34

Expert Comment

by:johnsone
ID: 40613032
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
 

Author Comment

by:xbox360dp
ID: 40613136
Sbstuber,

What do I need to do to change it?
0
 

Author Comment

by:xbox360dp
ID: 40613137
Sorry ... and get the results I'm after?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40614371
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question