[Cialug] mysql triggers -- figured them out

[Daniel.Juliano at wellsfargo.com]

Well, I couldn't tell you exactly when Access got stored procs, but
that's what our Access 'developers' tell me.
 
I've been lucky enough to have been coding against db's with stored
procs in multi-developer environments the last six years, and you'll see
that triggers make sense as the number of apps / devs using the database
scale.  You might also find that avoiding triggers + procs as much as
possible and including your data rules as part of the database design
(normalizing data and using constraints such as foreign keys) will save
a lot of headache.
 
=Dan

  _____  

From: cialug-bounces at cialug.org [mailto:cialug-bounces at cialug.org] On
Behalf Of carl-olsen at mchsi.com
Sent: Monday, October 23, 2006 12:29 PM
To: Central Iowa Linux Users Group
Subject: RE: [Cialug] mysql triggers -- figured them out


I've never used triggers before and I just started learning how to use
stored procedures (since they were not available until MySQL 5 was
released).  I didn't know MS Access has stored procedures.  See how
little I know?  You and the web application world have certainly moved
somewhere ahead of me.


	-------------- 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: [Cialu g] 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 maki ng 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 
	> 
	> 
	> _______________________________________________ 
	> C ialug 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/8224f49c/attachment-0001.htm