I see what you're saying. That won't really help, because I'll still be duplicating changes in the update and insert triggers every time I add a new field. The hope was that I would only need to do it once. Your method sounds like it could become costly from an execution time perspective if a lot of fields require the validation.
For example, one field has to be >= 0, so I put this validation If new.sector < 0 then Set new.sector := 0; End if; For the time being, I have that in two separate triggers, the INSERT and the UPDATE triggers. Where as Oracle would allow me to define the trigger as "on UPDATE or INSERT on <tablename>" I guess I'll have to do it the long way, and keep an eye out for changes to MySQL that will allow me to optimize it later. Dan. ________________________________ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 09, 2005 9:59 AM To: Burke, Dan Cc: mysql@lists.mysql.com Subject: Re: Triggers that handle multiple events (insert and update) "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
