loops and conditionals inside triggers

Posted by Ying on Stack Overflow See other posts from Stack Overflow or by Ying
Published on 2010-03-24T03:23:44Z Indexed on 2010/03/24 3:43 UTC
Read the original article Hit count: 339

Filed under:
|

I have this piece of logic I would like to implement as a trigger, but I have no idea how to do it! I want to create a trigger that, when a row is deleted, it checks to see if the value of one of its columns exists in another table, and if it does, it should also perform a delete on another table based on another column.

So say we had a table Foo that has columns Bar, Baz. This is what id be doing if i did not use a trigger:

function deleteFromFooTable(FooId)
{
  SELECT (Bar,Baz) FROM FooTable WHERE id=FooId
  if not-empty(SELECT * FROM BazTable WHERE id=BazId)
    DELETE FROM BarTable WHERE id=BarId

  DELETE FROM FooTable WHERE id=FooId

}

I jumped some hoops in that pseudo code, but i hope you all get where im going. It seems what i would need is a way to do conditionals and to loop(in case of multiple row deletes?) in the trigger statement. So far, I haven't been able to find anything. Is this not possible, or is this bad practice? Thanks!

© Stack Overflow or respective owner

Related posts about mysql

Related posts about mysql-triggers