[Cialug] mysql triggers -- figured them out

David Champion dchampion at visionary.com
Mon Oct 23 12:01:11 CDT 2006


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" <kevin at linuxsmith.com>: -------------- 
> 
> 
> 
>>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




More information about the Cialug mailing list