Trigger giving exception ORA-04091

[hemantmudaliar]

The Trigger is giving following exception

java.sql.SQLException: ORA-04091: table PSCONTENT.VGNASCHANNEL is mutating, trigger/function may not see it ORA-06512: at "PSCONTENT.CHECK_CHANNEL_UNPUBLISH", line 4 ORA-06512: at "PSCONTENT.CHECK_CHANNEL_UNPUBLISH_TR", line 1 ORA-04088: error during execution of trigger ................................

// Trigger
CREATE OR REPLACE TRIGGER check_channel_unpublish_tr
before delete
ON vgnaschannel
for each row
check_channel_unpublish( :old.id )
;
/



// Procedure
CREATE OR REPLACE procedure check_channel_unpublish( p_channelId in char ) as
n int;
begin
select count(*) into n
from vgnAsChannel
where parentChannelId = p_channelId;
if ( n > 0 ) then
raise_application_error(
num => -20052,
msg => ( 'attempt to unpublish channel that is referenced by still-published subchannels; id=' || p_channelId ) );
end if;
select count(*) into n
from vgnAsChannelFileAssociation
where channelId = p_channelId;
if ( n > 0 ) then
raise_application_error(
num => -20053,
msg => ( 'attempt to unpublish channel that has ' || n || ' still-published objects associated with it' ) );
end if;
end;
/

[debasisdas]

Your case is a mutating trigger error.

this happens when the trigger tries to modify the base table from which it is reading data.

[hemantmudaliar]

Your case is a mutating trigger error.

this happens when the trigger tries to modify the base table from which it is reading data.

Thanks for the reply.

But my problem is that, I cannot modify the Trigger neither can i disable it as this trigger is written in a CMS tool which i m using.

Is there any way of achieving the same functionality by writting any DB query/ procedure etc.

Hemant