Shulou(Shulou.com)05/31 Report--

This article shows you how to use triggers in Sql Server. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Sql Server creates two dedicated tables for each trigger: Inserted table and Deleted table. These two tables are maintained by the system, and they exist in memory rather than in the database. The structure of these two tables is always the same as that of the table acted on by the trigger, and after the trigger is executed, the two tables associated with the trigger are also deleted.

Operation on the table

Inserted logic table

Deleted logic table

Add record (insert)

Store additional records

None

Delete record (delete)

None

Store deleted records

Modify record (update)

Store updated records

Store pre-updated records

3. For, after, instead of trigger

After: triggers execute after the statement that triggered them is complete. If the statement fails due to an error, the trigger will not execute. An after trigger cannot be specified for a view, it can only be specified for a table. Multiple after triggers can be specified for each trigger action (insert, update, delete). If the table has multiple after triggers, you can use sp_settriggerorder to define which after trigger fires first and which fires last. With the exception of the first and last triggers, the firing order of all other after triggers is uncertain and uncontrollable.

For: equivalent to after

Instead of: this trigger executes instead of the trigger operation. Instead of triggers can be specified on tables and views. Only one instead of trigger can be defined for each trigger action (insert, update, delete). Instead of triggers can be used to perform enhanced integrity checks on data values provided in insert and update statements.

IV. The use of triggers

1. Create a trigger:

Createtriggertrigger_name

On {table_name | view_name}

{for | After | Insteadof}

[insert,update,delete]

As

Sql_statement

2. Delete trigger:

Droptriggertrigger_name

3. Check the existing triggers in the database:

Select*fromsysobjectswherextype='TR'

4. View a single trigger:

Execsp_helptext' trigger name'

5. Modify the trigger:

Altertriggertrigger_name

On {table_name | view_name}

{for | After | Insteadof}

[insert,update,delete]

As

Sql_statement

5. Examples of triggers

1. Set up a trigger in the Orders table. When inserting a record into the Orders table, check whether the item status status of the goods table is 1. Yes, the order cannot be added to the Orders table.

If (object_id ('tgr_orders_insert','tr') isnotnull) dropkeeper [TGR _ orders_insert]; gocreatekeeper [TGR _ orders_insert] on [orders] afterinsertasif (select [status] from [goods], [inserted] where [orders] .name) = 1beginprintships the goodsisbeingprocessedprints printed the ordercanbecommittedrollbacktransactionhands-rollback, avoid adding

End

The above is how to use triggers in Sql Server. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.