RE: Triggers that handle multiple events (insert and update)
A valid question. However that's only the simplest example of the kind of validation I'm trying to put into the triggers (for the sake of a shorter email). Dan. -Original Message- From: Björn Persson [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 09, 2005 5:25 PM To: mysql@lists.mysql.com Subject: RE: Triggers that handle multiple events (insert and update) onsdagen den 9 november 2005 18:07 skrev Burke, Dan: For example, one field has to be = 0, so I put this validation Why don't you just declare that field as unsigned? Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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
RE: Triggers that handle multiple events (insert and update)
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
RE: Triggers that handle multiple events (insert and update)
onsdagen den 9 november 2005 18:07 skrev Burke, Dan: For example, one field has to be = 0, so I put this validation Why don't you just declare that field as unsigned? Björn Persson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
