The use of MySQL stored procedures and triggers and the method of invocation in PHP

A stored procedure, as its name implies, is a set of SQL statements that are compiled and stored in a database in order to accomplish a particular function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.

       writing format for stored procedures:
  CREATE PROCEDURE [ owner stored procedure name [; program number
  [( #1,... parameter #1024)]
  [with
  {RECOMPILE | Encryption | RECOMPILE, encryption}
 ]
  [for REPLICATION]
  as 

where the stored procedure name cannot exceed -a word. Maximum settings per stored procedure1024x768a parameter
(SQL Server 7.0version above),the parameters are used in the following ways:
@parameter name data type[VARYING] [=Default Value] [OUTPUT]
there must be one before each parameter name"@"symbols,The parameters for each stored procedure are used internally for that program only,the type of the parameter in additionIMAGEoutside, otherSQL ServerThe supported data types are available for use.

Output : Indicates that this parameter can be passed back to the
with {recompile|encryption}
Recompile: indicates that each time this stored procedure is executed, it is recompiled once
Encryption: the contents of the stored procedure being created are encrypted

Using Stored procedures has the following advantages:
* the ability to store processes has greatly enhancedSQLthe function and flexibility of the language. Stored procedures can be written with flow control statements, with a strong flexibility to complete complex judgments and more complex operations.
* ensures the security and integrity of your data.
# stored procedures enable users who do not have permissions to access the database indirectly under control, thus guaranteeing the security of the data.
# stored procedures allow related actions to occur together to maintain the integrity of the database.
* before running the stored procedure, the database has been analyzed by syntax and syntax, and the optimized execution scheme is given. This well-compiled process can greatly improveSQLthe performance of the statement. Due to the executionSQLmost of the work on the statement has been completed, so the stored procedure can be executed at a very fast speed.
* you can reduce the amount of traffic on your network.
* put operational procedures that embody enterprise rules into the database server in order to:
# centralized control.
# change the stored procedure in the server when the enterprise rules change, without modifying any applications. Enterprise rules are characterized by frequent changes, and if the operational procedures that embody enterprise rules are put into the application, it is very important to modify the application workload when the Enterprise rules change (modify, release, and install the application). If you put the operations that embody enterprise rules into a stored procedure, when the enterprise rules change, as long as you modify the stored procedure, the application does not need any changes.

PHP Methods of operation

mysql_query ("Call stored procedure name")

Trigger

is executed internally by MySQL. , a trigger is a named database object related to a table that is activated when a specific event occurs on the table. For example, an event occurs when we insert a row of data into a table, or when a record is deleted.

Grammar:
CREATE TRIGGER trigger_name trigger_time trigger_event
On Tbl_name for Eachrow trigger_stmt
Trigger_time is the action time of the trigger. It can be before or after to indicate that the trigger is activating its
Trigger before or after the statement.
Trigger_event indicates the type of statement that activates the trigger. Trigger_event can be one of the following values:
Insert: Activates the trigger when inserting a new row into the table, for example, through INSERT, loaddata, and REPLACE statements;
Update: Activates a trigger when a row is changed, for example, through an UPDATE statement;
Delete: Activates the trigger when a row is deleted from the table, for example, through the delete and REPLACE statements.

The use of MySQL stored procedures and triggers and the method of invocation in PHP