SQL Server stored procedures and triggers

A. Stored procedures

1. Classification of stored procedures

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

Allows users to declare variables, perform conditionally, and have strong programming capabilities

Stored procedures can be grouped into two categories: system stored procedures, user stored procedures, and extensibility stored procedures

1). System Stored Procedures

A system stored procedure is a stored procedure provided by a SQL Server system that can perform various operations as a command.

System stored procedures are used primarily to get information from system tables and to help system administrators manage SQL Server.

Provides convenience for users to view database objects.

The system stored procedure is defined in the system database master, and its prefix is sp_. Does not have to be called before the stored procedure

Plus database name

2). User Stored Procedures

A user stored procedure is a storage that a user creates in a user database to accomplish a particular function, according to their needs.

Storage process.

3). Extended Stored Procedures

Extended stored procedures for dynamic link libraries performed outside the SQL Server environment (dll,dynamic-linklibrar-ies)

To achieve. Extended stored procedures are identified by the prefix "xp_" and are executed in a manner similar to stored procedures.

Two. Triggers

Triggers are automatically executed or activated when a particular event occurs, regardless of the user or application connected to the database. A table has up to three different types of triggers, using a trigger when the update occurs, a trigger when the delete occurs, and a trigger when the insert occurs

1. Differences from stored procedures

The main difference between triggers and stored procedures is the way triggers are run. A stored procedure must be called and executed by a user, an application, or a trigger, and a trigger is automatically executed or activated when a particular event occurs, regardless of the user or application connected to the database

Note: Although triggers are powerful, they can also be bad for server performance. Therefore, be careful not to put too many features in a trigger because it will slow down the response and increase the amount of time the user waits

2. Classification of triggers

1). DDL triggers

DDL triggers are invoked when a data definition language (DDL) event occurs in a server or database

2). DML triggers

DML triggers are actions to perform when data manipulation language (DML) events occur in the database server

3. Two kinds of special tables: Deleted table and inserted table

SQL Server 2008 creates two special tables for each TRIGGER statement: The deleted table and the inserted table.

This is a two logical table that is created and maintained by the system and cannot be modified by the user. They are stored in memory without

is in the database. The structure of the two tables is always the same as the structure of the table that the trigger acts on. After the trigger execution completes,

The two tables associated with the trigger are also deleted

The Delete table holds all rows that are deleted from the table by executing the DELETE or UPDATE statement. In the execution dele

When the TE or update operation, the deleted rows are moved from the table triggering the trigger to the Delete table, and the tables are not

There will be a common line.

The Insert table holds all the rows that are inserted into the table by the execution Inseret or UPDATE statement. In the execution of the INS

In ERT or update transactions, new rows are added to the table and insert table that triggers the trigger, inside the Insert Table

The tolerance is a copy of the new row in the table that triggers the trigger.

Note: An update transaction can be considered to perform a delete operation before an insert operation, the old row is first moved to the delete table, and the new row is inserted into both the table and the Insert table that triggers the trigger.