[Cialug] mysql triggers -- figured them out

[carl-olsen at mchsi.com]

I happen to come from a MySQL 4.1 and MySQL 3.2 background, in which triggers and stored procedures didn't exist.  As I said, I hadn't figured out a reason to use triggers yet.  It sounds like you and Dave just gave me a couple of good reasons to use them.

-------------- Original message from <Daniel.Juliano at wellsfargo.com>: -------------- 


> To further what Dave is saying, if you ever code another page /app that 
> works with the same table(s), what are the chances you'll remember to 
> call all the stored procs you're relying on to enforce data integrity? 
> 
> You don't happen to come from an MS Access background, do you? Access 
> likes stored procs for select / insert / update /delete, but that's 1995 
> methodology speaking, and the web application world has mostly since 
> moved on. 
> 
> =Dan 
> 
> -----Original Message----- 
> From: cialug-bounces at cialug.org [mailto:cialug-bounces at cialug.org] On 
> Behalf Of David Champion 
> Sent: Monday, October 23, 2006 12:01 PM 
> To: Central Iowa Linux Users Group 
> Subject: Re: [Cialug] mysql triggers -- figured them out 
> 
> Triggers always fire, but if you put these kinds of rules in a Stored 
> Procedure, they only fire when you call the SP. Example: if another web 
> page inserts a record or does an update without using the SP (say 
> another developer adds a page...) then your SP rules don't get applied. 
> 
> Using the Trigger, even if you just enter a record using phpMyAdmin, or 
> the mysql console, the rules will be enforced. 
> 
> -dc 
> 
> carl-olsen at mchsi.com wrote: 
> > I thought about checking into it for you, but I haven't really figured 
> out a reason to use triggers yet. I seem to be able to do the same 
> things using stored procedures, such as checking another table before 
> doing an insert, update or delete to see if a certain condition is true 
> or false. I like being able to do this stuff inside the database 
> instead of making a bunch of diffent PHP queries. It seems to take less 
> typing to do it all in a stored procedure. I'm glad you figured it out. 
> I bought a good book, if you're interested, "MySQL Store Procedure 
> Programming" by O'Reilly, ISBN 0-596-10089-2. It also has a chapter on 
> triggers. 
> > 
> > Carl Olsen 
> > http://www.carl-olsen.com/ 
> > 
> > 
> > -------------- Original message from "Kevin C. Smith" 
> > : -------------- 
> > 
> > 
> > 
> >>Since I got no response I thought some might be interested in the 
> solution. 
> >>After reading the docs it turns out to be fairly simple. Yes, I didn't 
> 
> >>read the docs before; I was trying speed it up by getting pointed to 
> >>the TIMEDIFF function. To get the time difference of two fields using 
> >>a trigger. 
> >> 
> >>CREATE TRIGGER trigger_time BEFORE INSERT ON time_table FOR EACH ROW 
> >>SET NEW.time_diff = TIMEDIFF(NEW.end_time, NEW.begin_time); 
> >> 
> >>Of course an ON UPDATE trigger is also needed. 
> >> 
> >> 
> >>-- 
> >>Kevin C. Smith 
> >> 
> >>_______________________________________________ 
> >>Cialug mailing list 
> >>Cialug at cialug.org 
> >>http://cialug.org/mailman/listinfo/cialug 
> >> 
> >> 
> >>---------------------------------------------------------------------- 
> >>-- 
> >> 
> >>_______________________________________________ 
> >>Cialug mailing list 
> >>Cialug at cialug.org 
> >>http://cialug.org/mailman/listinfo/cialug 
> 
> 
> _______________________________________________ 
> Cialug mailing list 
> Cialug at cialug.org 
> http://cialug.org/mailman/listinfo/cialug 
> 
> 
> _______________________________________________ 
> Cialug mailing list 
> Cialug at cialug.org 
> http://cialug.org/mailman/listinfo/cialug 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://cialug.org/pipermail/cialug/attachments/20061023/49ef6c9a/attachment.htm