[Cialug] mysql triggers -- figured them out

[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" 
> <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


_______________________________________________
Cialug mailing list
Cialug at cialug.org
http://cialug.org/mailman/listinfo/cialug