?
Solved

Database Trigger not displaying correct results?

Posted on 2015-02-11
6
Medium Priority
?
226 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

752 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