SQL Server Stored Procedures and triggers

I. stored Procedure 1. stored Procedure classification a stored procedure is a database object that is stored in a database and can be executed by an application through a single call. It also allows users to declare variables and execute conditions, stored procedures with strong programming functions can be divided into two types: system stored procedures, user stored procedures, and extended stored procedures 1 ). system Stored Procedure System

I. stored Procedure 1. stored Procedure classification a stored procedure is a database object that is stored in a database and can be executed by an application through a single call. It also allows users to declare variables and execute conditions, stored procedures with strong programming functions can be divided into two types: system stored procedures, user stored procedures, and extended stored procedures 1 ). system Stored Procedure System

1. Stored Procedure

1. Storage Process Classification

A stored procedure is a database object that is stored in a database and can be executed by an application through a single call.

Allows users to declare variables and perform conditional operations. It has strong programming functions.

There are two types of stored procedures: system stored procedures, user stored procedures, and extended stored procedures.

1). system stored procedures

System stored procedures are stored procedures provided by the SQL Server system and can be used as commands to perform various operations.

The system stored procedure is used to obtain information from the system table and help the system administrator manage SQL Server,

It is convenient for users to view database objects.

System stored procedures are defined in the system database master with the prefix sp _. You do not need to use the stored procedure before calling

Add Database Name

2). User stored procedures

User Stored Procedures refer to the storage

Storage process.

3). extended storage process

Dynamic link library (DLL, Dynamic-LinkLibrar-ies) that extends the stored procedure to be executed outside the SQL Server Environment)

. Extended stored procedures are identified by the prefix "xp _", which are executed in a similar way as stored procedures.

Ii. triggers

A trigger is automatically executed or activated when a specific event occurs. It has nothing to do with users or applications connected to the database .. A table can have up to three types of triggers. One trigger is used when UPDATE occurs; one trigger is used when DELETE occurs; and one trigger is used when INSERT occurs.

1. Differences from stored procedures

The main difference between a trigger and a stored procedure lies in the running mode of the trigger. Stored Procedures must be explicitly called and executed by users, applications, or triggers. triggers are automatically executed or activated when a specific event occurs, it is irrelevant to the user or application connected to the database.

Note: despite the powerful functions of triggers, they may also be harmful to server performance. Therefore, be careful not to place too many functions in the trigger, because it will reduce the response speed and increase the user waiting time.

2. trigger classification

1). DDL trigger

DDL triggers are called when a Data Definition Language (DDL) event occurs on the server or database.

2). DML triggers

DML triggers are operations performed when a data operation language (DML) event occurs on the database server.

3. Two special tables: DELETED table and INSERTED Table

SQL Server 2008 creates two special tables for each trigger statement: DELETED table and INSERTED Table.

The two logical tables are created and maintained by the system. Users cannot modify them. They are stored in the memory instead

Is in the database. The structure of these two tables is always the same as that of the table to which the trigger is applied. After the trigger is executed,

The two tables related to the trigger will also be deleted.

The DELETE table stores all rows to be deleted from the table by executing the DELETE or UPDATE statement. In the execution of DELE

During the TE or UPDATE operation, the deleted rows are moved from the trigger table to the DELETE table.

There are common rows.

The insert table stores all rows to be inserted into the table by executing the INSERET or UPDATE statement. Execute INS

In the ERT or UPDATE transaction, new rows are added to the trigger table and the INSERT table at the same time.

A replica of a new row in the table that triggers the trigger.

Note: An UPDATE transaction can be regarded as executing a DELETE operation first and then an INSERT operation. The old row is first moved to the DELETE table, and then the new row is inserted to the table and the INSERT table that triggers the trigger at the same time.