Database optimization Create stored procedures, triggers

Stored procedures speed up the execution of queries, increase the speed of access to data, and help you achieve modular programming, preserve consistency, and improve security. Triggers are stored procedures that are automatically executed when an INSERT, UPDATE, delete operation is made to a table, and are typically used to enforce business rules.

One, stored procedures

1. Why a stored procedure is required

Sending SQL code to the server from the client over the network and executing it is unsafe, giving hackers the opportunity to steal data, as shown in a simple SQL injection process

From the know, the application process is not secure, mainly in the following aspects:

(1) The data is not secure, the network transmits the SQL code, is easy to be intercepted by the unauthorized person

(2) Every commit SQL code is executed after syntax compilation, which affects the performance of the application

(3) Large network traffic, for the repeated execution of the SQL code, on the network multiple transmission, affecting the network traffic

2. What is a stored procedure

Stored procedures are precompiled collections of SQL statements and control statements that are stored in the database, can be executed by application calls, and allow the user to declare variables, logical control statements, and other powerful programming features. Contains logical control statements and data manipulation statements that can receive parameters, output parameters, return single or multiple result values, and return values

Advantages of using Stored procedures:

(1) Modular programming, only need to create once, you can call the stored procedure any time

(2) Fast execution speed and high efficiency

(3) Reduce network traffic

(4) Good safety

Stored procedures are divided into system stored procedures and user-defined stored procedures

3. System stored Procedures

is a set of precompiled T-SQL statements that provide a mechanism for managing databases and updating tables and acting as a shortcut to retrieving information from system tables

(1) common system stored procedures

The name of the system stored procedure begins with "sp_" and is stored in the resource database

The syntax for using stored procedures is as follows:

exec stored procedure name [parameter value]

For example, execute the following T-SQL statement

(2) commonly used extended stored procedures

An extended stored procedure is a class of various system stored procedures provided by SQL Server that allows external stored procedures to be created using other programming languages such as C #, usually beginning with "Xp_", in the form of a single DDL

A common extended stored procedure is xp_cmdshell, which can perform some actions under DOS commands, such as creating folders and listing folders. The syntax is as follows:

EXEC xp_cmdshell DOS command [no_output]

Where No_output is an optional parameter, setting whether to output return information after executing DOS command

For example: Create a folder bank under C and view the file

4. User-defined stored procedures

In addition to using the system's stored procedures, you can create your own stored procedures. You can use SSMS or T-SQL statements to create stored procedures

(1) Create a stored procedure using SSMS

(2) Creating a stored procedure using T-SQL statements

The syntax for creating a stored procedure is as follows:

The syntax for deleting a stored procedure is as follows:

drop proc Stored Procedure name

Case: The following two tables, writing stored procedures, to achieve the network management professional average score

Trigger

A trigger is a special stored procedure that is called automatically when data in a table is updated in response to an INSERT, UPDATE, DELETE statement

1. What is a trigger

Triggers are stored procedures that are automatically executed when a table is inserted, updated, deleted, and typically used to enforce business rules, and can define constraints that are more complex than check constraints. Triggers are executed primarily through event triggering, and stored procedures can be used directly by stored procedure names.

2. Classification of triggers

Insert trigger: Triggers when data is inserted into the table

UPDATE trigger: Fires when a column or columns in a table are updated

Delete Trigger: When a record in the delete table is triggered

3. Deleted table and inserted table

Each trigger has two special logical tables: Delete table and insert table. Managed by the system, stored in memory rather than in the database, so the user is not allowed to modify it directly. They only temporarily store changes to the data rows in the table, and when the trigger is done, they are also deleted.

4. Effects of triggers

The main role is: the implementation of the primary key and foreign keys can not guarantee the complex referential integrity and data consistency, in addition to the following several functions

(1) Enforcing constraints: implementing more complex constraints than check constraints

(2) Tracking changes: Detect operations within the database, thus not allowing unauthorized updates and changes

(3) Cascade operation: Detect the operation in the database and automatically cascade affect the contents of the whole database

5. Create a Trigger

Create triggers can use SSMS or T-SQL statements

(1) Create a trigger using SSMS

(2) Creating a trigger using T-SQL statements

The syntax for creating triggers using T-SQL statements is as follows:

Create TRIGGER trigger name//created trigger names on table name//table or view name on which the trigger was executed [with encryption] Optional, prevents the trigger from being published as part of SQL Server replication for {[delete,insert,update]}//keyword, specifying at least one item, if multiple, separated by commas as SQL statement

Case: Create a trigger that prompts a message when someone changes the message and blocks the action

If you need to modify the trigger, you can do this by modifying the T-SQL statement in the popup window

Considerations when creating triggers

(1) Create trigger must be the first statement in the batch and can only be applied to one table

(2) Triggers can only be created in the current database, but may refer to external objects of the current database

(3) In the same CREATE TRIGGER statement, you can define the same trigger action for a variety of user actions, such as delete

Database optimization Create stored procedures, triggers