Principles and Application Skills of SQL Server triggers

1. 'essential' of a trigger ':

A trigger is a special stored procedure. It cannot be explicitly called. Instead, it inserts records, changes records, or deletes records into a table. When an event occurs, automatically activated.

2. The 'function ':

The trigger can be used to implement complex integrity constraints on the table to maintain data consistency. When the data protected by the trigger changes, the trigger is automatically activated, the response also executes certain operations (operations on other related tables) to ensure the data integrity constraints or incorrect modifications.

A trigger can query other tables and execute complex T-SQL statements. The trigger and the command that triggers the trigger to execute are treated as a transaction, so it has all the features of the transaction.

Note: 'What are the characteristics of a transaction? What is the role of a trigger? 'If you find that the T-SQL statement that causes the trigger to execute an illegal operation, such as the correlation operation on other tables, and find that the data is lost or the data to be called does not exist, then, roll back to the SQL Server database status before the event is executed.

3. trigger function:

The trigger can be used to cascade changes to the database.

Note: 'relationship and difference between triggers and constraints'

(1) In general, the use of constraints is more efficient than the use of triggers.

(2) At the same time, the trigger can complete more complex restrictions than the check constraint.

Note:

2.1 different from the check constraint, other tables can be referenced in the trigger.

2.2 trigger: you can find that the data inconsistency between the two tables is changed and perform corresponding operations based on these differences.

2.3 different triggers can be used for different operations (insert, update, and delete) on a table. Different triggers can be called to complete different operations even for the same statement.

Example 1: the inventory of goods should be reduced when an order is signed.

Question? What features does the trigger have? Can check constraints be solved?

Example 2: orders cannot be placed for goods being sorted.

Question? What features does the trigger have? Can check constraints be solved?

4. Analysis of trigger operations:

On SQL Server, two dedicated tables are created for each trigger: inserted Table and deleted table.

These two logical tables are maintained by the system. They exist during execution and disappear at the end of the trigger.

What is the purpose of this?

The specific operation steps and procedures are as follows:

(1) The deleted table stores all rows to be deleted from the table due to executing the delete or update statement.

When performing the delete or update operation, the deleted rows are moved from the table on which the trigger is activated to the deleted table. The two tables do not have the same rows.

(2) The inserted Table stores all rows to be inserted into the table for executing the insert or update statement.

When executing insert or update, the new row is added to the active trigger table and the inserted Table at the same time. The content of the inserted Table is a copy of the new row in the table that activates the trigger.

Note: The update transaction can be viewed as executing a delete operation first and then an insert operation. The old row is first moved to the deleted table, add the new row to the active trigger table and the inserted Table at the same time.

11.1.3 instead of and after triggers

It mainly includes the definition and application scope conditions, and the operation execution time;

11.2 create a trigger

1. Consider why design the starters to solve what problems?

2. content to be formulated: Why do you think about it? I don't know. Let's take a look at the following example!

T-SQL statement create trigger

Syntax structure:

The following is a reference clip:
Create trigger name
On Table or view
For | after | instead of -- operation time
Insert, update, delete
As
SQL statement

Job:

(Requirement: create two tables in the northwind table: cust_test and order_test)

The following is a reference clip:
Cust_test: customerid char (5) PK order_test:
Customerid char (5)- ing
Custcity orderid PK
Custname ordernames
Cstatus int ostatus int -- Status
Cstorage int orders int -- purchase quantity and inventory
Cdate date odate date -- Date

Job 1:
Create a delete trigger in the cust_test table to achieve cascading deletion of the above two tables.

Job 2:
Create an insert trigger in the order_test table. When a row is inserted into the order_test table, if the status value of the corresponding record in the cust_test table is 1, the data cannot be written in the preparation status.

The following is a reference clip:
Answer 1:
Use northwind
Go
Create trigger cust_orders_del1
On cust_test
After Delete
As
Delete from order_test
Where customerid in
(Select customerid from deleted)
Go

Answer 2:
Use northwind
Go
Create trigger cust_orders_ins2
On order_test
After insert
As
If (select cstatus from cust_test, inserted
Where cust_test.customerid = inserted. customerid) = 1
Begin
Print 'the goods is being processed'
Rollback transaction
End
Go

Graphical operation trigger

11.3 view trigger status

Graphical operations combined with T-SQL commands

(1) sp_helptrigger trigger name
View the trigger name, owner, and five boolean values.

The following is a reference clip:
Supdate, isdelete, isinsert, isafter, isinsteadof

(2) sp_helptext trigger name
View text information

(3) set the validity and re-Validity of a trigger

The following is a reference clip:
Invalid:
Use northwind
Alter table table name
Disable trigger name

Valid again:
Use northwind
Alter table table name
Enable trigger name
(4) Delete A trigger

The following is a reference clip:
Use northwind
Drop trigger name, trigger name

Job 3:
Create an insert trigger on the order_test table to reduce the inventory of the corresponding goods in the cust_test table when adding an order.

Job 4:
Create an insert trigger on the order_test table. The specified order date (odate) cannot be modified manually.

Job 5:
The required items must be in the warehouse and the quantity is sufficient.

Job 6:
When an insert trigger is created on the order_test table and multiple rows of data are inserted at the same time, the purchased items must exist in the warehouse.

Answer 3:

The following is a reference clip:
Use northwind
Go
Create trigger cust_orders_ins3
On order_test
After insert
As
Update cust_test
Set cstorage = cstorage-inserted.orders
From cust_test, inserted
Where cust_test.customerid = inserted. customerid

Answer 4:

The following is a reference clip:
Use northwind
Go
Create trigger orderdateupdate
On order_test
After update
As
If Update (odate)
Begin
Raiserror ('error', 10, 1)
Rollback transaction
End

Answer 5:

The following is a reference clip:
Use northwind
Go
Create trigger order_insert5
On order_test
After insert
As
Begin
If (select count (*)
From cust_test, inserted
Where cust_test.customerid = inserted. customerid) = 0
Begin
Print 'NO ENTRY in goods for your order'
Rollback transaction
End
If (select cust_test.cstorage from cust_test, inserted
Where cust_test.customerid = inserted. customerid) <
(Select inserted. Orders from cust_test, inserted
Where cust_test.customerid = inserted. customerid)
Begin
Print 'no enough entry in goods for your order'
Rollback transaction
End
End

Answer 6:

The following is a reference clip:
Use northwind
Go
Create trigger order_insert6
On order_test
After insert
As
If
(Select count (*) from cust_test, inserted
Where cust_test.customerid = inserted. customerid) <> @ rowcount
-- The @ rowcount function can be used in the trigger logic to differentiate single-row insertion and multi-row insertion.
Begin
Delete order_test from order_test, inserted
Where order_test.orderid = inserted. orderid and
Inserted. customerid not in (select customerid from cust_test)
End

Print @ rowcount

Transact-SQL reference
Set rowcount
Enable Microsoft? SQL Server? Stop processing the query after the specified number of rows is returned.
Syntax
The following is a reference clip:
Set rowcount {number | @ number_var}

Parameters
The following is a reference clip:
Number | @ number_var

The number of rows (integers) to be processed before a given query is stopped ).
Note
We recommend that you rewrite the delete, insert, and update statements currently using set rowcount to use the top syntax. For more information, see Delete, insert, or update.
Ignore the set rowcount option settings for insert, update, and delete statements executed on remote tables and local and remote partition views.
To disable this option to return all rows, set rowcount to 0.
Setting the set rowcount option will stop most Transact-SQL statements after they are affected by a specified number of rows. This includes data modification statements such as triggers, insert, update, and delete. The rowcount option is invalid for dynamic cursors, But it limits the row set and does not perceive cursors of the key set. Exercise caution when using this option, which is mainly used with the SELECT statement.
If the number of rows is small, set rowcount replaces the top keyword of the SELECT statement.
Set rowcount is set during execution or running, rather than during analysis.
Permission
The Set rowcount permission is granted to all users by default.
Example
Set rowcount stops processing after the specified number of rows. In the following example, note that row x meets the condition that the prepayment is less than or equal to $5,000. However, it can be seen from the number of rows returned by the update that not all rows are processed. Rowcount affects all Transact-SQL statements.
The following is a reference clip:
Use pubs
Go
Select count (*) as CNT
From titles
Where advance >=5000
Go

The following is the result set:
The following is a reference clip:
CNT
-----------
11

(1 row (s) affected)

Now, set rowcount to 4 and update all rows whose prepayment is equal to or greater than $5,000.
Set rowcount to 4.
Set rowcount 4
Go
Update titles
Sets advance = 5000
Where advance >=5000
Go