lulubell-b
asked on
Mutating table
Trigger worked as expected, but now i have a new requirement. Table ais has 2000 plus records, table usage identifies records in ais of interest to a program. My initial attempt included the delete in the after statement, but I removed it as i kept encountering a mutating table error. So my thought is to only focus on the update statement. You see the insert works fine as my customer wishes to see all new ais, but on update or delete- only the records of type 'DEF' need to be accounted for in the stage table
The below trigger compiles, but the mutating error occurs when testing. Help needed on a solution, thank you!
The below trigger compiles, but the mutating error occurs when testing. Help needed on a solution, thank you!
CREATE OR REPLACE TRIGGER S_U_C_ID_SEQ_TR
after update on C
for each row
declare
c_chng_dsc_tx_v s_c.a_chng_dsc_tx%type;
ABC_GRP_V number;
begin
select COUNT(*) into ABC_GRP_V
FROM ais a, usage b
where b. usg_nm = 'DEF'
and :old.id = b. id;
if ABC_GRP_V > 1 then
if updating (ID')
or updating ('NM')
or updating ('ACRYNM_TX')
then
if nvl(to_char(:old. ID),'null')!=nvl(to_char(:new.ID), 'null') then
c_chng_dsc_tx_v := c_chng_dsc_tx_v || ID, ';
if nvl (:old.NM, 'null')!=nvl(:new. NM, 'null') then
c_chng_dsc_tx_v := c_chng_dsc_tx_v || 'NM, ';
end if;
if nvl (:old. ACRYNM_TX, 'null')!=nvl(:new. ACRYNM_TX, 'null') then
c_chng_dsc_tx_v := c_chng_dsc_tx_v || ACRYNM_TX, ';
end if;
insert into s_C
(S_C_ID,
ID,
NM,
ACRYNM_TX,
Ab_ID,
Ab_ TYP,
c_chng_cd,
c_mod_c_dt,
c_d_src,
c_chng_dsc_tx,
c_cnfrm_rcpt_flg)
values
(S_C_ID_SEQ.NEXTVAL,
:old. id,
:new. NM,
:new. ACRYNM_TX,
:new.Ab_ID,
:new.Ab_TYP,
'U',
sysdate,
'ABC',
c_chng_dsc_tx_v,
'N');
end if;
end if;
END;
/SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
nevermind, somehow I convinced myself that you were updating the ais table. sorry for the confusion.
are there any other triggers on "C"?
you should still look at the select above too since it's a cartesian product.
That is, there is no join condition between ais and usage.
are there any other triggers on "C"?
you should still look at the select above too since it's a cartesian product.
That is, there is no join condition between ais and usage.
ASKER
Thank you for your quick reply and oh my I have no idea how to build 'a package associative array and then use an after statement trigger instead of after row trigger to process them.' I may be overstepping the ROE (rules of engagement), but can you help me accomplish your recommendation?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I will certainly take a look, give it a try, and get back with you. My sincere thanks!
sorry it took so long to get back to you. My recommendation above only applied when I thought your trigger was on the AIS table and you were then updating (mutating) the AIS table in the trigger.
You code "as is" doesn't show any possibility for a mutating error on C. So, there must be something else going on. Possibly triggers on other tables involved in this trigger or other triggers on C. Since this trigger both selects and updates AIS, I suggest you look for triggers on that table. We may be pursuing the right problem in the right way but on the wrong object.
You code "as is" doesn't show any possibility for a mutating error on C. So, there must be something else going on. Possibly triggers on other tables involved in this trigger or other triggers on C. Since this trigger both selects and updates AIS, I suggest you look for triggers on that table. We may be pursuing the right problem in the right way but on the wrong object.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are right, I do have a separate insert trigger. After looking at the requirement again, I am thinking that only one compound trigger is needed meaning I should add to this one after insert, update, or delete. The stage table should only fire on those records that are of name 'DEF'. I have a meeting scheduled shortly that will bog me down for the rest of the morning, but I'll give it a try on my return. That should take care of the issue, you think?
depends on what happens in the insert trigger and on which table it is.
Since this is an update trigger on C. A separate trigger on inserts of C shouldn't really matter except for a merge statement (which could potentially fire both) and even if they did both fire it will depend on what happens in each to see if they will collide.
Since this is an update trigger on C. A separate trigger on inserts of C shouldn't really matter except for a merge statement (which could potentially fire both) and even if they did both fire it will depend on what happens in each to see if they will collide.
ASKER
I dropped the insert trigger on C for grins and on the trigger supporting the usage table. Ran the above script, compiled committed and mutating. :) Your statement above was correct, it does not seem that insert matters.
Can I put the "select COUNT(*)..." section in a block by itself so that it is the first thing the trigger does? Like run the select, load the value into a variable using "select COUNT(*) into..." then commit to release the select before moving on to the insert... I not sure if I am on to something, but that's what i am thinking.
I am trying to get smarter at this stuff, thanks again for your support
Can I put the "select COUNT(*)..." section in a block by itself so that it is the first thing the trigger does? Like run the select, load the value into a variable using "select COUNT(*) into..." then commit to release the select before moving on to the insert... I not sure if I am on to something, but that's what i am thinking.
I am trying to get smarter at this stuff, thanks again for your support
No, splitting the count into a separate block won't help.
first, let's identify exactly where the error is occuring.
Comment out the select count(*) and instead simply assign ABC_GRP_V to 0 and 2 (recompile and test with each value)
first, let's identify exactly where the error is occuring.
Comment out the select count(*) and instead simply assign ABC_GRP_V to 0 and 2 (recompile and test with each value)
ASKER
I am so sorry, I must go but will try your suggestion tomorrow as it will be wonderful to get this off my plate and have a better understanding. I added my script builds and below is sample data for you to scan to see if there is something there I am missing. I thought it might help, again thanks and have a good evening.
Sample data
--------------------------
ID NM ACRYNM_TX AB_ID AB_TYP
1 program name prg_nm asasdf1232435 108
2 test program tst_prg
3 sys name sys_nm 3453425asdfsdf 106
--------------------------
USGTST_ID ID USG_NM NM ACRYNM_TX
1 1 DEF
2 2 program test PT
3 3 DEF sys test SYT
Sample data
--------------------------
ID NM ACRYNM_TX AB_ID AB_TYP
1 program name prg_nm asasdf1232435 108
2 test program tst_prg
3 sys name sys_nm 3453425asdfsdf 106
--------------------------
USGTST_ID ID USG_NM NM ACRYNM_TX
1 1 DEF
2 2 program test PT
3 3 DEF sys test SYT
CREATE TABLE C
(id NUMBER NOT NULL,
nm VARCHAR2(255),
acrynm_tx varchar2(255),
ab_id VARCHAR2(32),
AB_TYP NUMBER,
CONSTRAINT id_pk PRIMARY KEY (id))
TABLESPACE DATATOOLS
-------
CREATE TABLE s_C
(S_id NUMBER NOT NULL,
id NUMBER,
nm VARCHAR2(255),
acrynm_tx varchar2(255),
ab_id VARCHAR2(32),
AB_TYP NUMBER,
c_chng_cd VARCHAR2(1),
c_mod_c_dt DATE,
c_d_src varchar2(5),
c_chng_dsc_tx VARCHAR2(500),
c_cnfrm_rcpt_flg varchar2(1),
CONSTRAINT S_id_pk PRIMARY KEY (S_id))
TABLESPACE DATATOOLS
========
CREATE SEQUENCE s_id_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
=======
CREATE SEQUENCE id_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE
NOCYCLE;
========
CREATE TABLE usage
(usgtst_id NUMBER NOT NULL,
id NUMBER,
USG_nm VARCHAR2(255),
nm VARCHAR2(255),
acrynm_tx varchar2(255),
CONSTRAINT usgtst_id_pk PRIMARY KEY (usgtst_id))
TABLESPACE DATATOOLSASKER
My deepest apologizes for letting this issue lag for so long. I am finally able to re-engage. Do I close this discussion and initiate a new one? Please advise.
how did the suggestion work?
ASKER
My co-worker removed the trigger on the table being queried and he tells me it's working as expected. I believe your comments were helpful in pointing us toward the solution. I have been out of the office for month or so.
You will see another post from me taking on a similiar trigger, but using a cursor approach.
You will see another post from me taking on a similiar trigger, but using a cursor approach.
so, this question is done then? if so please close it before opening a new one.
if not, I'll wait for the next post, but I'm not sure what you're trying to achieve now.
if not, I'll wait for the next post, but I'm not sure what you're trying to achieve now.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select COUNT(*) into ABC_GRP_V
FROM ais a, usage b
where b. usg_nm = 'DEF'
and :old.id = b. id;
looks like that could simply become...
select COUNT(*) into ABC_GRP_V
FROM usage b
where b. usg_nm = 'DEF'
and :old.id = b. id;