I have two tables AUTHOR
and BOOK
connected by AUTHORID
.
I need to create a trigger that in case of deleting an author, it deletes the books by that author first and after that deletes the author. And if someone just tries to update the AUTHORID
field in AUTHOR
it updates the AUTHORID
in BOOK
automatically.
Is that even doable? :)
set term # ;
create trigger del for author
before delete or update as
declare variable aut int;
declare variable bok int;
begin
if(deleting) then
begin
delete from book where authorid=:aut;
delete from author where authorid=:aut;
end
if (updating) then
begin
update book set authorid=new.authorid;
end end#
set term ; #
You don't need a trigger to do this. For deleting you can use a foreign key that is
ON DELETE CASCADE
. This will automatically cascade the delete to the dependent row if the foreign key target is deleted.In general I'd advise against allowing people to change identifiers, but if you really need or want that you can use
ON UPDATE CASCADE
. This will automatically update the foreign key if the foreign key target changes.So for example (copied/modified from the Interbase 6 Language Reference):