Firebird trigger before delete

Question

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 ; # 

Depolarize

Answer 1

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):

CREATE TABLE T1 (P1 INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE T2 (F2 INTEGER REFERENCES T1(P1)
  ON UPDATE CASCADE
  ON DELETE CASCADE);

Rhadamanthus

ty for the answer, the assignment says that I have to make a trigger. Do you think it can be done or is it a trick question? :D – Depolarize

It can be done with triggers but it wouldn't be multi user safe solution, right way is to use constraints. Since it is an shool assignment(?) I won't write the triggers for you, I only say you need one before delete and one after update trigger, for the syntax see firebirdsql.org/file/documentation/reference_manuals/… – Determinate

@Determinate Why would a trigger not be safe where a constraint is? On an implementation level constraints in Firebird are checked by a system trigger (on the table with the foreign key target) – Rhadamanthus

Right, it should be perfectly safe, I think I was confused when I posted my previous comment... – Determinate

@Depolarize If you really need to write a trigger, then you should update the question with more information (what did you try, what doesn't work or where you are stuck). Just plain saying 'I don't know' looks like pleading for us to do your homework. – Rhadamanthus

Right, sorry. I don't know how to tell the trigger to delete the entries in the BOOK table that are connected with the AUTHOR. if someone would try to delete author with AUTHORID=1 then how would the trigger know which rows in BOOK to delete. We've been doing psql for 2 weeks now and I didn't even know you could program in firebird before that. I've been searching for a solution to this for 2 days now and haven't found anything... – Depolarize

Please put that in your question, not in comments. – Rhadamanthus