Oracle: Delete row in a table using triggers while deleting value in another table

oracletrigger

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?

Best Answer

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.