Row-Level Trigger with unexpected results

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.

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

Select allOpen 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?

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

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

Select allOpen in new window

slightwv,

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

Thanks again for your help!!!!

slightwv is the BEST ... period!

Glad to help.

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

*sigh*  I do love free drinks...  ;)