Use stored procedures and triggers to manage databases

This article mainly introduces how to use the stored procedures and triggers in the database background to manage the database technology, and uses Delphi as the front-end and SQL Server as the background mode to provide specific implementation code.

I. SQL interactive database query language

In interactive database query language SQL, there are four basic SQL statements related to table operations: (the syntax form and description of the four statements are given below respectively)

(1) query table commands

 

SELECT[ALL|DISTINCT]select_list [INTO[new_table_name]] [FROM{table_name|view_name} [WHERE clause] [GROUP BY clause] [HAVING clause] [ORDER BY clause] [COMPUTE clause]

Where:

 

Select_list specifies the column (all indicates all columns, distinct: do not select the same record new_table_name specified target table name table_name | view_name specified source table name or source view name where clause gives the selection condition group by clause processing having clause by clause group conditions for grouping order by clause sort by clause compute clause generates a new line

(2) Insert record command

 

INSERT[INTO] {table_name|view_name}[(cohurnn_list)] {DEFAULT VALUES|values_list|select_statement}

Where:

 

Table_name | view_name specifies the new table name or the new view name values_list | select_statement specifies the column or subquery.

(3) Modify Table life-saving

 

UPDATE{table_name|view_name} SET[{table_name|view_name} {column_list|variable_list|variable_and_column_list}[WHERE clause]

Where:

 

Table_name | view_name specifies the source table name or source view name column_list | variable_list indicates the WHERE clause of the column or variable name to modify conditions.

 

(4) Delete commands

 

DELETE[FROM]{table_name|view_name} [WHERE clause]

Where:

 

Table_name | view_name specifies the source table name or source view name where clause to provide the modification conditions.

Ii. Use of stored procedures and triggers in SQL Server

Stored procedures are pre-compiled SQL statements stored on the server. The following issues must be taken into account during use:

1. The stored procedure performs a syntax check during the first compilation. The compiled stored procedure is saved in the cache for calling. This improves the execution speed and efficiency.

2. The stored procedure is activated by the application and cannot be automatically executed by SQL Server.

3. A stored procedure can be used to collect and modify data, but not both.

The advantages of stored procedures are:

1. Improve efficiency when executing repetitive tasks;

2. Make the front-end applications share the application logic;

3. It can be created permanently or temporarily;

4. It can be automatically executed when SQL Server is started.

The statement syntax for creating a stored procedure is:

 

CREATE PROCedure[owner.]procedure_name[;number] [(parameter1[,parameter2]...[parameter255])] [{FOR REPLICATION}|{WITH RECOMPILE} [{[WITH]|{,}ENCRYPTION]] AS sql_statements

Where:

Proceddure_name is the process name

; Number: used for serial numbers when the process name is repeated

[(Parameter1 [, parameter2]... [parameter255])] is the parameter sequence.

Whth recompile in the cache where the execution plan is not saved, each execution process needs to be re-compiled.

Encryption encrypt the contents of the syscomments table. The syscomments table contains the text of create procedure, so that the syscomments table is not deleted at any time.

For replication processes are executed at the front end, not on the server

The following SQL statement creates the Stored Procedure my_store_pro1 ON THE mydatabase

Use mydatabase

 

 

The following must be an independent query module, because the create procdure statement must be the first line of the query module.

 

Create procdure success @ my_paral char, @ my_para2 int as select * From my_table1 where my_table1.no1 = @ my_para1 and my_table.1no2 <= @ my_para2 go execution stored my_store_pro1 exec comment '12' 23

 

A trigger is a special stored procedure that is automatically executed whenever the table it protects is modified. The trigger is automatically executed by SQL Server and cannot be called by applications, so as to protect the integrity and completeness of the database. Its syntax structure is:

 

Create trigger [owner.] trigger_name on [owner.] table_name for {insert, update, delete} [with encr yption] As if Update (column_name) [{and | or} Update (column_name)...] SQL _statements, where: trigger_name 〓 specify the trigger name table_name 〓 specify the name of the table where the trigger is located insert, update, delete 〓 specify the trigger conditions

 

Encryption encrypt the contents of the syscomments table. The syscomments table contains the createprocedure text, so that the syscomments table is not deleted at any time. SQL _statementw is modified in the table content (update) the following action is a modification trigger. If the nolmy_table1 field of my_tabel is modified, an error message is displayed.

 

Create trigger test on my_tablel for update as if Update (NOL) Begin print (this column of data cannot be modified ') End

When data is added to title_l, the following insert trigger assigns a value to the last no_local record. The value is the maximum value of no_local in the existing record plus 1 (no_local is a string type)

 

CREATE TRIGGER add_no 　　ON TITLE_L 　　FOR INSERT 　　AS 　　DECLARE @tmpl int 　　SELECT @tmpl=MAX(CONVERT(int,NO_LOCAL))FROM TITLB_L 　　SELECT @tmpl=@tmpl+1 　　DECLARE @tmpstr char(4) 　　SELECT @tmpstr=CONVERRT(varchar(4),@tmpl) 　　UPDATE TITLE_L 　　SET NO_LOCAL=@tmpstr WHERE NO_LOCAL=NULL

Iii. Introduction to Application Instances

The following section describes the function of calling a stored procedure in the foreground Delphi environment. ALL: select the table DBO for generating records that match the user identity. today; some records that do not meet the date requirements are deleted by the trigger, and DBO is moved from the front-end with batch records. on the local localdata database where the content of today is downloaded. DBF table.

{Create a stored procedure my_store_prol on the mydata database of the server :}

 

Create procdure my_store_prol @ secu_id int as select * from all where my_table1.no1 <= @ secu_id go {table DBO in mydata database. create trigger on today:} create trlgger add_no on today for insert as delete * form today where riqi go

 

{Execute the Stored Procedure my_store_prol:} in the foreground program :}

 

Databasel.AliasName:='MYDATA'; 　　Databasel.DatabaseName;='my_database'; 　　Database1.connected;=True; 　　SourceTable1.DatabaseName;='my_database'; 　　SourceTable1.TableName='dbo.today'; 　　SourceTable1.Active;=True; 　　StoredProc1.DatabaseName;='My_database'; 　　StoredProc1.StoredProcName:='my_proc'; 　　StoredProc1.Params.Clear; 　　StoredProc1.Params.CreateParam(ftInteger,'secu_id',ptInput); 　　StoredProc1.Prepare; 　　StoredProc1.ExecProc;

 

{Download the content of DBO. Today in the foreground program to the Data. DBF table :}

 

Database2.AliasName:='LOCALDATA'; 　　Database2.DatabaseName:='local_data'; 　　Database2.connectde:=True; 　　DestinTable1.DatabaseName:='local_data'; 　　DestinTable1.TableName:='data.dbf; 　　DestinTable1.Active:=True; 　　BatchMovel.Mode:=batAppend; 　　BatchMovel.RecordCount:=0 　　BatchMovel.Source:=SourceTable1; 　　BatchMovel.Destination:=DestinTable1; 　　BatchMovel.Execute;