Solved

Incorrect results in Database Trigger.

Posted on 2015-02-16
5
190 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 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Sbstuber,

What do I need to do to change it?
0
 

Author Comment

by:xbox360dp
Comment Utility
Sorry ... and get the results I'm after?
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Need help with Oracle syntax 4 39
Pl/SQL Query 31 61
EXECUTE IMMEDIATE 5 33
Need a replacement data type in Oracle 6 24
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now