"Burke, Dan" <[EMAIL PROTECTED]> wrote on 11/09/2005 09:05:50 AM:
> Hello, > > Can triggers in 5.0 handle multiple events? I have some range checking > I'd like to enforce at the database level when inserting or updating a > record, and I'm finding myself duplicating trigger code to make an > INSERT and an UPDATE trigger, which makes it harder to maintain in the > long run, especially considering I want to do this for multiple tables. > > I looked through the documentation and couldn't find any example, and > then did some experimenting, but I could not get what I'm looking for. > > It could be the fact that I'm stuck in an Oracle mentality when it comes > to triggers, so maybe there's a different way to do what I want to do > without duplicating code. Like can I pass the "new" object to a > procedure and do the checking in there? (what would be the syntax to > create that procedure if it's possible?) That way I could still have > one only place to do the range/limit checking. > > I dislike duplicating code (mainly because I know myself). Inevitably > I'll forget to update one or the other place and get burned for it when > I least suspect it. But if this is a symptom of mysql triggers being a > young still, where is the best place to submit a feature request? :) > > Thanks, > Dan. > I wouldn't use a stored procedure but a FUNCTION to do the validation. The trigger fires for each row and there isn't a row-type data type so you are going to need to pass into the function each field you need to evaluate. Overall, I don't see why you have to write two functions but you will need to define two triggers and point each of them to the common validation function. http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
