Solved

Database Trigger not displaying correct results?

Posted on 2015-02-11
6
212 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
  • 3
  • 3
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
Comment Utility
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
Comment Utility
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
Comment Utility
Did my post above not help to find the issue ?
0
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.

 

Author Comment

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

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 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

10 Experts available now in Live!

Get 1:1 Help Now