Row-Level Trigger with unexpected results

Gurus,

I'm having issues getting the correct results with my row-level trigger.

create or replace
TRIGGER UPDATE_ITEM_ART1_SCO1 before
  INSERT OR
  UPDATE ON SCO 
  FOR EACH ROW 
  DECLARE 
  scokey number;
  element_key number;
  VARIANT_KEY NUMBER;
  ARTICLE_FIELD_1 VARCHAR2(2000);
  CALC_NAME VARCHAR2(2000);

begin
      select distinct :new.scooszkeyi, prokeyi , oszvarkeyi,
      scrbezc,  listagg(artanrc,',') within group (
    order by to_number(artanrc) asc) 
    INTO SCOKEY, ELEMENT_KEY, VARIANT_KEY,
      Calc_name, 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 prokavkeyi = artkavkeyi
    AND OSZOBJKEYI      = PROKEYI
    AND OSZTYPS         = 170
    AND SCRBEZC NOT LIKE '%!%'
    GROUP BY :NEW.SCOOSZKEYI,PROKEYI, OSZVARKEYI,
      scrbezc;
    
UPDATE ART
    SET =  calc_name||'('||article_field_1||')'
    WHERE ARTKAVKEYI = VARIANT_KEY 
    and artkeyi = :new.SCOOBJKEYI; 
  

  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    ARTICLE_FIELD_1 := ARTICLE_FIELD_1;
    VARIANT_KEY     := VARIANT_KEY;
    END;

Open in new window


The problem is with the following join in my trigger:
and aezartkeyi = :NEW.SCOOBJKEYI

Current results:

Item#| ARTVF1C  
47205|Price Range(47205)
15666|Price Range(15666)
20284|NULL

If I remove the join above from the trigger:

Results

Item#| ARTVF1C  
47205|Price Range(47205,15666,20284)
15666|Price Range(47205,15666,20284)
20284|NULL

Here are the results I want:

Expected Results

Item#| ARTVF1C  
47205|Price Range(47205,15666)
15666|Price Range(47205,15666)
20284|NULL

I only want to get the results for the two item# being affected when the trigger executes. How can I get this result? Would a statement level trigger work?
xbox360dpAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Going from knowledge of your two previous questions.

>>Current results:

This is because of the row level trigger.    When you use the IN clause in the update the trigger fires once for each row updated.  So, you are getting the last one updated.

>>If I remove the join above from the trigger:

I assume one of those tables is the global temp table?

I had the listagg and select as two different selects in the last question.  Not sure if it makes a difference or not.

Just like the last question: We'll need a LOT more information about the tables and statements.

A sample test case that we can set up on our side for testing works best so we can post tested code.
xbox360dpAuthor Commented:
slightwv,

I assume one of those tables is the global temp table?
No, I got rid of the temp tables completely.

As far as more information:

ART = Item table
PRO = Item group table
AEZ = lookup table for ART & PRO

Example:

select artkeyi, prokeyi from pro, art, aez
where aezprokeyi = prokeyi and aezartkeyi = artkeyi;

ARTKEYI|PROKEYI
47205|3098
15666|3098
20284|3098

This will give you the list of items and the group it belongs too.

SO in the trigger when I use:

and aezartkeyi = :NEW.SCOOBJKEYI

Open in new window


:NEW.SCOOBJKEYI is art.artkeyi

I completely understand why I receive the results I do ... being that this is a row level trigger.

By removing this join I get all items(artkeyi) assigned to their group(prokeyi).

Does this help?
slightwv (䄆 Netminder) Commented:
>>No, I got rid of the temp tables completely.

That might be the problem?

>>By removing this join I get all items(artkeyi) assigned to their group(prokeyi).

You may need additional columns to restrict the results to listagg so you only get the values you want.

>>Does this help?

Nope.

It would take WAY to long for you to explain your tables and data to a level that I would understand them to help by describing it.

That is why a closely related simple test case works best.  I can see in the data what you want and you know how you can take that and apply it to your system.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

xbox360dpAuthor Commented:
slightwv,

Here is another example:

Create tables and insert records:

 
SQL> CREATE TABLE ART_TEMP  (ARTKEYI NUMBER, ARTBEZC VARCHAR2(30),artanrc varchar2(30), ARTVF1C VARCHAR2(30));
 
Table created.
 
SQL> CREATE TABLE PRO_TEMP  (PROKEYI NUMBER, PROBEZC VARCHAR2(30), PROVF1C VARCHAR2(30)) ;
 
Table created.
 
SQL> CREATE TABLE AEZ_TEMP  (LOOKUP_ARTKEYI NUMBER, LOOKUP_PROKEYI number);
 
Table created.
 
SQL> CREATE TABLE SCO_TEMP  (SCOKEY NUMBER, scofield varchar2(30),calc_name varchar2(30));
 
Table created.
 
SQL> Insert into ART_TEMP  (ARTKEYI,ARTBEZC,artanrc, ARTVF1C) values (3002,'shoes','47027',null);
 
1 row created.
 
SQL> Insert into ART_TEMP  (ARTKEYI,ARTBEZC,artanrc, ARTVF1C) values (3003,'socks','47026',null);
 
1 row created.
 
SQL> Insert into ART_TEMP  (ARTKEYI,ARTBEZC,artanrc,ARTVF1C) values  (3001,'pants','47025',null);
 
1 row created.
 
SQL> Insert into PRO_TEMP (PROKEYI,PROBEZC,PROVF1C) values (4001,'outfit',null);
 
1 row created.
 
SQL> Insert into AEZ_TEMP  values (3001,4001);
 
1 row created.
 
SQL> Insert into AEZ_TEMP values (3002,4001);
 
1 row created.
 
SQL> Insert into AEZ_TEMP values (3003,4001);
 
1 row created.
 
SQL> Insert into SCO_TEMP values (3001,null,'Price Range');
 
1 row created.
 
SQL> Insert into SCO_TEMP values (3002,null,'Price Range');
 
1 row created.
 
SQL> Insert into SCO_TEMP values (3003,null,'Price Range');
 
1 row created.
 
SQL> commit;

Commit complete.

Trigger:

create or replace
TRIGGER Trigg_Test_SCO before
  insert or
  update on sco_temp for each row
  declare
  item varchar2(30);
  name_of_calc varchar2(30);
  pro_name varchar2(30);
  begin
select distinct  :new.calc_name,
listagg(artanrc,',') within group (
    order by artanrc),
      probezc into  name_of_calc, item , pro_name 
    from pro_temp, art_temp, aez_temp
    where lookup_artkeyi = artkeyi
    and  lookup_prokeyi = prokeyi
    and lookup_artkeyi = :new.scokey
    group by :new.calc_name, probezc;
   
    update art_temp set artvf1c = name_of_calc||'('||item||')'
    where artkeyi = :new.scokey;
    end;
 
Update sco_temp to fire trigger:
 
update sco_temp set scofield = 'TEST2'
where scokey in (3001,3002);
commit;
 
Query to get results:
 
select artkeyi, artvf1c from art_temp
order by artkeyi;
 
Current Result:
 
3001	Price Range(47025)
3002	Price Range(47027)
3003	
 
The result I want:
 
3001	Price Range(47025,47027)
3002	Price Range(47025,47027)
3003

Open in new window

slightwv (䄆 Netminder) Commented:
You need to think about when the trigger fires and what information you have at that time.

Still need the global temp table.

This works on the sample you posted.

create global temporary table mygtt(tmpvalue varchar2(30)) on commit delete rows; 

create or replace TRIGGER Trigg_Test_SCO before
  insert or
  update on sco_temp for each row
  declare
  item varchar2(30);
  name_of_calc varchar2(30);
  pro_name varchar2(30);
  v_my_agg varchar2(30);
  begin

  	insert into mygtt values(:new.scokey);

	select listagg(artanrc,',') within group(order by artanrc)
	into  item
	from pro_temp, art_temp, aez_temp
	where lookup_artkeyi = artkeyi
    	and  lookup_prokeyi = prokeyi
    	and lookup_artkeyi in (select tmpvalue from mygtt);

    update art_temp set artvf1c = :new.calc_name || '(' || item || ')'
    where artkeyi in (select tmpvalue from mygtt);
    end;
/

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xbox360dpAuthor Commented:
slightwv,

You are my favorite person right now. If I knew you personally drinks would be on me tonight.

Thanks again for your help!!!!
xbox360dpAuthor Commented:
slightwv is the BEST ... period!
slightwv (䄆 Netminder) Commented:
Glad to help.

>>If I knew you personally drinks would be on me tonight.

*sigh*  I do love free drinks...  ;)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.