asked on

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.

Naveen Kumar

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;

xbox360dp

ASKER

I've requested that this question be deleted for the following reason:

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

Naveen Kumar

Did my post above not help to find the issue ?

xbox360dp

ASKER

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?

xbox360dp

ASKER

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?

ASKER CERTIFIED SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial