Triggers and Stored Procedures.

A place for users and developers of the Xataface to discuss and receive support.

Postby Aoirthoir » Tue Jul 11, 2006 6:22 am

MySQL (and other SQL's) has the ability to apply triggers to adds, updates and deletes. Thus one can specify certain SQL statements to be performed whenever any of those events occur. Now this is on a table by table basis. Thus there are not (as far as I can tell) global triggers. Which means if there is a trigger you want to occur on every delete for every table, then you have to create a trigger for every table. But those triggers can perform any SQL (again as far as I can tell).

Stored procedures are a little different. They are just series of SQL commands which are stored along with the database. They can be called and run as if you had typed all of the SQL commands. Even triggers can call stored procedures. They offer a convenience so you do not have to type the same commands over and over. They offer others as well. For instance, there are times we wish to do things in dataface, there will be other times one of us needs to write a routine directly in PHP. Or perhaps we need to use a different product for some reason. Well with stored procedures, we can be sure that certain techniques follow the database.

We have decided to use dataface mostly for the data entry, validation, retrieval and display of our data. While as much as possible we want to use SQL itself for the manipulation and logic of our data. So for instance if there were a way to store relatioships in SQL, and dataface could directly access that, I would do so. Thus the relationships are maintained across all products I use. Also for instance with totalling fields as on an order, this too can be done in SQL.

A lot of times it will be a mix though. For instance review my post on the History technique we are attempting to implement. Also the tagging system. (When I have these fully done I will give Mr. Steve the details so we can post a How-To)

Triggers and procedures I believe will offer us all two main benefits when using Dataface. First is portability and abstraction. More work being done by the server than by the php code. Second hopefully thus, is speed.

Personally as we create these triggers and procedures and add them to the How-tos. I will consider them to be parts of Dataface. Also if the code is generic enough (for instance if I am able to create classes in SQL? so that the stored procedure can apply to any table, such as the History Stored Procedure being able to be one procedure but stores histories for all tables) then little routines could be placed in .sql files which would allow any of us to simply run the SQL file into our own copies of MySQL from the command line and have those procedures stored and ready for our use.
Posts: 420
Joined: Wed Dec 31, 1969 5:00 pm

Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 1 guest

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved