Trigger usage tips

Trigger

1. 'essential' of a trigger ':

A trigger is a special stored procedure and cannot be explicitly called,

When an event occurs, a record is inserted, modified, or deleted into the table.

Automatically activated.

2. The 'function ':

Triggers can be used to implement complex integrity constraints on tables and maintain the number

Data Consistency. When the data protected by the trigger changes, the trigger is automatically activated,

Perform operations (operations on other related tables) at the same time to ensure data integrity.

Constraints or incorrect modifications.

A trigger can query other tables and execute complex T-SQL statements. Trigger and reference

The commands executed by the trigger are treated as a transaction, so they have all the features of the transaction.

Note: 'What are the characteristics of a transaction? What is the role of a trigger? '

If an illegal operation is performed on the T-SQL statement that causes the trigger to execute, such as

If the correlation operation finds that the data is lost or the data to be called does not exist, roll back to the event for execution.

The status of the SQL Server database.

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 The trigger can find that the data inconsistency between the two tables is changed, and the corresponding

.

2.3 different operations (insert, update, and delete) on a table can be performed using different triggers.

The producer can call different triggers 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.

During the delete or update operation, the deleted rows are moved from the table that activated the trigger to the deleted row.

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 operations, 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 regarded as executing a delete operation first and then an insert operation. The old row first

Moved to the deleted table to 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:

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)

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

The record status value is 1, indicating that the data cannot be written in the preparation status.

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.

Supdate, isdelete, isinsert, isafter, isinsteadof

 

(2) sp_helptext trigger name

View text information

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

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

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.

 

Example 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:

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:

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:

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:

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

Set rowcount {number | @ number_var}

 

Parameters

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 from processing when they have been 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.

Use pubs
Go
Select count (*) as CNT
From titles
Where advance >=5000
Go

 

The following is the result set:

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