sqlserver--Trigger

　　A: basic knowledge of triggers

　　A trigger is a special kind of stored procedure, but without interfaces (input and output parameters), triggers are part of the transaction that raises them because SQL Server automatically throws them as an implicit transaction, so that when the trigger is executed correctly, the transaction is considered complete. Triggers can also be rolled back and forth through rollback TRAN within the trigger, and when the triggered SQL statement is an implicit transaction, only the SQL statements and triggers that raised the trigger are rolled back; All operations starting with the outermost begin Tran are undone when an explicit transaction occurs. So triggers are often used to implement complex integrity constraints and enforce business rules on a table, which is an advanced constraint that is defined on a particular table and references columns in other tables. You can define constraints that are more complex than check constraints. For example, you cannot enforce integrity rules, audit, and maintain non-canonical data with constraints.

　　1, the classification of the trigger

Triggers from SQL statement types can be categorized as DML triggers and DDL triggers.

• DML triggers: Automatically triggered when inserts, update, delete are in the table, and DML triggers can be divided into Instead of triggers and after triggers from the triggering action

• • • Instead of triggers: Also known as substitution triggers, can be used only with code that replaces the user-submitted DML operation (INSERT, UPDATE, DELETE), which executes only the trigger code, without executing the user-submitted code. Acting on a table or view; An operation can have only one instead of trigger.
    • After trigger: Executes the trigger code after responding to user code (both DDL and DML operations are available). The trigger is triggered by a statement rather than by a row, that is, the after trigger can only be triggered once, regardless of how many rows are affected by a statement, and can only be used for tables; An operation may have multiple after triggers, using sp_ The settriggerorder stored procedure marks the first execution and last execution of the trigger, and if the triggered SQL statement violates the constraint, the After trigger is not triggered, but it is possible because the instead of trigger is triggered before the triggering SQL statement is executed. So instead OF triggers can perform some preprocessing on constraints.

• DDL triggers: Triggers on create, DROP, alter, etc. for tables, views, stored procedures, and so on.

2. Inserted table and Deleted table of Trigger

These two tables are maintained by the system, and they exist in memory rather than in the database. The structure of the two tables is always the same as the structure of the table that the trigger acts on. When the trigger executes, the two tables associated with the trigger are also deleted . the Deleted table holds all rows that are to be removed from the table because of execution of the DELETE or UPDATE statement. The inserted table holds all rows to be inserted into the table because of an INSERT or UPDATE statement execution. The user can query the two temporary tables with the SELECT statement, but no modifications are allowed.

Inserted tables and Deleted tables are not indexed, so when the data in the table is large and the data in the table is frequently referenced, you should save the data to a temporary table and create an index for the staging table.

For example, I set up a post trigger on the table. Insert the data into another table after inserting the data; when I execute the Insert data, I execute the trigger statement and insert the data into another table . here's the problem. What happens when I insert 10 consecutive data triggers? The difference between SQL Server and MySQL is that SQL Server does not allow statements within a trigger until the 10 insert SQL statement is executed.

Operations on a table	Inserted	Update	Deleted
Inserted table	New data to insert	Updated data	X
Deleted table	X	Pre-update data	The deleted data

3. The number of rows affected.

The following statement:

Select @c1 =c1, @c2 =c2, @c3 =c3 from Inserted

If there is no row affected by the SQL statement that triggered the trigger, the value of the variable @c1, @c2, @c3 will not change, it is still the original value, and when more than one row is affected, each row modifies the value of the variable, and finally, the value of the variable is just the value of the last row. Therefore, you must first determine the number of rows affected.

The @ @rowcount function holds the number of rows affected by the previous statement, or uses select @count =count (1) from Inserted to determine the number of rows in the Inserted table.

　　

　　

　　

Example 1:

Create Trigger Tr_1

On database

For create_table--| | Drop_table

As

RAISERROR (' You can't create a table ', 16, 1)

Rollback--can be rolled back because the create_table is an implicit transaction

Drop trigger tr_1 on database–-must have on database when deleting a trigger indicates that the trigger

DML triggers:
A DML trigger is invoked when a data manipulation language (DML) event occurs in a database operation.

DML triggers can be divided into the following 3 types:
1.AFTER triggers: After triggers can only be specified on the table (post-Trigger)
After executing the Insert Update Delete statement operation, the after trigger is executed after the same as the development for.
2.INSTEAD of Trigger (override trigger)
Can be defined on a view or table but a INSTEAD of Insert/update/delete trigger can have only one
3.CLR trigger. Can be the above 2 or DDL triggers

DML triggers are subdivided into 4 types
Insert Trigger
Delete Trigger
UPDATE trigger
Several types of hybrid accessory triggers

① A table can have multiple post-triggers, but only one alternative trigger view can have only alternate triggers that cannot have a post-trigger

After trigger (post-trigger)

Example 1:

if exists (select * from sysobjects where name= ' tr_3 ')

Drop Trigger Tr_3

Go

Create Trigger Tr_3

On Employee

For update–-jumps to the statement block we defined later when the UPDATE statement is executed

As

If Update (CardID) –-determines whether a column has been changed

RAISERROR (' You do not change card number ID ', 16, 1)

Example 2:

if exists (select * from sysobjects where name= ' tr_4 ')

Drop Trigger Tr_4

Go

Create Trigger Tr_4

On Employee

For delete

As

RAISERROR (' Cannot delete employees ', 16, 1)

② the so-called post-trigger is to execute the corresponding operation before executing the statement block within the trigger we defined

INSTEAD of triggers (alternative triggers):

Example 1:

if exists (select * from sysobjects where name= ' tr_5 ')

Drop Trigger Tr_5

Go

Create Trigger Tr_5

On Employee

Instead of delete– replaces delete event

As

SELECT * from deleted--see what's in the temporary deleted table

Delete from pay where CardID in (select CardID from deleted)--Delete the sub-table and delete the base table first

Execute delete from Employee where CardID in (select CardID from deleted)

③[the so-called substitution trigger is to execute the block of statements within our defined trigger without performing the corresponding operation

Inserted temporary tables and deleted temporary tables used by triggers: [both post-trigger and override-trigger generate these 2 temporary tables]:

Virtual table inserted virtual table deleted
Put the updated data into the inserted temporary table when you perform the update and put the original data into the deleted temporary table
Put inserted data into inserted temp table when insert is executed
Execute delete/Drop the deleted data into the deleted temp table

L. Two tables have the same structure as the original data table of the activation trigger.
2. When inserting a record activation trigger with an INSERT statement, the system automatically inserts the record into the inserted temporary table while the record is inserted in the original table.
3. When deleting a record activation trigger with a DELETE statement, the system adds the deleted record to the deleted temporary table while the record is deleted from the original table.
4. When modifying the data activation trigger with the UPDATE statement, the system first deletes the original record in the original table, the deleted record is added to the deleted temporary table, and then the new record is inserted and inserted into the inserted temporary table.
5. The user can query the two temporary tables with the SELECT statement, but no modifications are allowed.
6. Once the trigger is executed, the two tables will be automatically deleted.

The trigger principle for SQL Server is:

For example, I set up a post trigger on my table. Insert this data into another table after inserting the data

A statement that executes a trigger when I execute one of the insert data inserts data into another table

The question is, what happens when I insert 10 consecutive data triggers?

The difference between SQL Server and MySQL is that SQL Server does not allow statements within a trigger until the 10 insert SQL statement is executed.

And MySQL is a row-level trigger, which is inserting a piece of data to execute the statement inside the trigger.

sqlserver--Trigger