1

So, I basically have those 3 tables:

tbl_albums ----> (id, name)

tbl_album_has_songs ----> (id_album, id_song)

tbl_songs -----> (id, name)

I need to write a trigger that deletes a row from table tbl_album when there are no more songs associated with that album on tbl_album_has_songs.

To do this, I need to count how many times a certain tbl_albums.id = tbl_album_has_songs.id_album appears on tbl_album_has_songs. If count is 0, then I delete the album from tbl_albums.

I've made the following trigger:

CREATE OR REPLACE TRIGGER delete_album after update or delete on tbl_album_has_songs
REFERENCING OLD AS old NEW AS new
FOR EACH ROW
declare lv NUMBER;
begin
select count(*) into lv from tbl_album_has_songs where id_album =:old.id_album;
if lv = 0 then 
delete from tbl_album where album.id = :old.id_album;
end if;
commit;
end;

but when I tried to delete some songs from tbl_album_has_songs it throws the following error:

Error starting at line 1 in command:
INSERT INTO tbl_equiporder VALUES (1,101,'11-sep-13',1000,1)
Error report:
SQL Error: ORA-04091: table S24585181.TBL_EQUIPORDER is mutating, trigger/function may not see it
ORA-06512: at "S24585181.TRIGGER_EQUIPORDER", line 9
ORA-04088: error during execution of trigger 'S24585181.TRIGGER_EQUIPORDER'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

Any help?

1 Answer 1

2

You can't select or modify the underlying table of the trigger during trigger processing. In this case, you are selecting.

Don't perform this work in a trigger. Manage the delete in PL/SQL application code where you can perform the delete, count for the children, and then delete the parent when there are no children. Basically what I'm interpreting your logic to be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.