[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Database Trigger not displaying correct results?

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
xbox360dp
Asked:
xbox360dp
  • 3
  • 3
1 Solution
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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
 
xbox360dpAuthor Commented:
I've requested that this question be deleted for the following reason:

I'm going to create a new question with more detail.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Did my post above not help to find the issue ?
0
Technology Partners: 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!

 
xbox360dpAuthor Commented:
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
 
xbox360dpAuthor Commented:
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
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now