Solved

Database Trigger not displaying correct results?

Posted on 2015-02-11
6
224 Views
Last Modified: 2015-02-18
Gurus,

A trigger a wrote isn't displaying the correct results.

create or replace 
TRIGGER UPDATE_ITEM_ART1_SCO before
  INSERT OR
  UPDATE ON SCO FOR EACH ROW DECLARE 
  article_key number;
  VARIANT_KEY NUMBER;
  ARTICLE_FIELD_1 VARCHAR2(2000);
  BEGIN
    SELECT DISTINCT listagg(artkeyi,',')  within group (order by artanrc),OSZVARKEYI,
      SCRBEZC
      ||'('
      ||LISTAGG(ARTANRC,',') WITHIN GROUP (
    ORDER BY ARTANRC)
      ||')'
    INTO article_key, VARIANT_KEY,
      ARTICLE_FIELD_1
    FROM SCC,
      SCR,
      OSZ,
      SSZ,
      ART,
      AEZ,
      PRO
    WHERE PROKEYI       = AEZPROKEYI
    AND ARTKEYI         = AEZARTKEYI
    AND :new.SCOOSZKEYI = OSZKEYI
    AND AEZARTKEYI      = :new.SCOOBJKEYI
    AND OSZSCCKEYI      = SCCKEYI
    AND SSZSCCKEYI      = SCCKEYI
    AND SSZSCRKEYI      = SCRKEYI
    AND OSZVARKEYI      = AEZKAVKEYI
    AND OSZVARKEYI      = ARTKAVKEYI
    AND OSZVARKEYI      = PROKAVKEYI
    AND OSZOBJKEYI      = PROKEYI
    AND OSZTYPS         = 170
    GROUP BY oszvarkeyi,
      scrbezc;
   UPDATE ART
    SET ARTVF1C    = ARTICLE_FIELD_1
    WHERE artkavkeyi = variant_key and artkeyi like article_key;

Open in new window


When I look at the ART table at column ARTVF1C I get:

 Your Choice(123)

Should get:

Your Choice(123,456)

So the concentration isn't working in my update statement. Why?

If run the select statement by itself I get the correct data.
0
Comment
Question by:xbox360dp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40605212
Just put some dbms_output.put_line(....) to debug the trigger code I,e to print the values of the below variables to see what is happening before the update ART statement gets reached.

I have added debug statements to your trigger code. so just recreate and test it out for one record...

create or replace
TRIGGER UPDATE_ITEM_ART1_SCO before
  INSERT OR
  UPDATE ON SCO FOR EACH ROW DECLARE
  article_key number;
  VARIANT_KEY NUMBER;
  ARTICLE_FIELD_1 VARCHAR2(2000);
  BEGIN
    dbms_output.put_line('Debug 1:New.ScoobjkeyI='|| :new.scoobjkeyI);
    dbms_output.put_line('Debug 2:New.scooszkeyI='|| :new.scooszkeyI);

    SELECT DISTINCT listagg(artkeyi,',')  within group (order by artanrc),OSZVARKEYI,
      SCRBEZC
      ||'('
      ||LISTAGG(ARTANRC,',') WITHIN GROUP (
    ORDER BY ARTANRC)
      ||')'
    INTO article_key, VARIANT_KEY,
      ARTICLE_FIELD_1
    FROM SCC,
      SCR,
      OSZ,
      SSZ,
      ART,
      AEZ,
      PRO
    WHERE PROKEYI       = AEZPROKEYI
    AND ARTKEYI         = AEZARTKEYI
    AND :new.SCOOSZKEYI = OSZKEYI
    AND AEZARTKEYI      = :new.SCOOBJKEYI
    AND OSZSCCKEYI      = SCCKEYI
    AND SSZSCCKEYI      = SCCKEYI
    AND SSZSCRKEYI      = SCRKEYI
    AND OSZVARKEYI      = AEZKAVKEYI
    AND OSZVARKEYI      = ARTKAVKEYI
    AND OSZVARKEYI      = PROKAVKEYI
    AND OSZOBJKEYI      = PROKEYI
    AND OSZTYPS         = 170
    GROUP BY oszvarkeyi,
      scrbezc;

    dbms_output.put_line('Debug 3:article key='|| article_key);
    dbms_output.put_line('Debug 4:variant key='|| variant_key);
    dbms_output.put_line('Debug 5:article_field_1='|| article_field_1);

   UPDATE ART
    SET ARTVF1C    = ARTICLE_FIELD_1
    WHERE artkavkeyi = variant_key and artkeyi like article_key;
0
 

Author Comment

by:xbox360dp
ID: 40617095
I've requested that this question be deleted for the following reason:

I'm going to create a new question with more detail.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 40613589
Did my post above not help to find the issue ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:xbox360dp
ID: 40613967
Hi nav_kum_v,

In the trigger query I'm pulling a single value from one row of the ART table. If I execute the query manually I'm pulling all rows. The question is how do I do this at the trigger level?
0
 

Author Comment

by:xbox360dp
ID: 40614571
Hi nav_kum_v,

How do I read dbms_output.put_line when the trigger fired when I complete an action through a third party app?
0
 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 500 total points
ID: 40615996
Ok.. I see. If that it the case, then you can change it to insert into debug table etc instead of dbms_output.put_line(..)

So create a dummy debug/log table first and then insert into it in the same place as dbms_output.put_line(..) as shown below... you can inserts accordingly for all the places or wherever you need.

create or replace
 TRIGGER UPDATE_ITEM_ART1_SCO before
   INSERT OR
   UPDATE ON SCO FOR EACH ROW DECLARE
   article_key number;
   VARIANT_KEY NUMBER;
   ARTICLE_FIELD_1 VARCHAR2(2000);
   BEGIN

     dbms_output.put_line('Debug 1:New.ScoobjkeyI='|| :new.scoobjkeyI);
     insert into my_debug_table values('Debug 1:New.ScoobjkeyI='|| :new.scoobjkeyI );

     dbms_output.put_line('Debug 2:New.scooszkeyI='|| :new.scooszkeyI);
     -- similarly here, you can add insert into for every dbms_output.put_line(..)

     SELECT DISTINCT listagg(artkeyi,',')  within group (order by artanrc),OSZVARKEYI,
       SCRBEZC
       ||'('
       ||LISTAGG(ARTANRC,',') WITHIN GROUP (
     ORDER BY ARTANRC)
       ||')'
     INTO article_key, VARIANT_KEY,
       ARTICLE_FIELD_1
     FROM SCC,
       SCR,
       OSZ,
       SSZ,
       ART,
       AEZ,
       PRO
     WHERE PROKEYI       = AEZPROKEYI
     AND ARTKEYI         = AEZARTKEYI
     AND :new.SCOOSZKEYI = OSZKEYI
     AND AEZARTKEYI      = :new.SCOOBJKEYI
     AND OSZSCCKEYI      = SCCKEYI
     AND SSZSCCKEYI      = SCCKEYI
     AND SSZSCRKEYI      = SCRKEYI
     AND OSZVARKEYI      = AEZKAVKEYI
     AND OSZVARKEYI      = ARTKAVKEYI
     AND OSZVARKEYI      = PROKAVKEYI
     AND OSZOBJKEYI      = PROKEYI
     AND OSZTYPS         = 170
     GROUP BY oszvarkeyi,
       scrbezc;

     dbms_output.put_line('Debug 3:article key='|| article_key);
     dbms_output.put_line('Debug 4:variant key='|| variant_key);
     dbms_output.put_line('Debug 5:article_field_1='|| article_field_1);

    UPDATE ART
     SET ARTVF1C    = ARTICLE_FIELD_1
     WHERE artkavkeyi = variant_key and artkeyi like article_key;

I thought you are testing your trigger and hence expected that you may be manually firing the update command in the sqlplus or toad and hence gave that dbms_output.put_line(..) which would write to the screen directly.

Thanks,
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

691 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