SQL Server -- trigger

In the previous blog, we learned and summarized the stored procedure. Today we learn a special stored procedure-trigger. As the name suggests, a trigger is executed when an event is triggered. At present, we also summarize the three aspects of its overview, Implementation, and Management.

 

I. Overview:

 

1. Concept: A trigger is a special type of stored procedure that triggers execution when inserting, deleting, or modifying data in a specific table.

Generally, certain business rules can be executed to maintain data integrity, check data validity, and implement Database

Manage tasks and some additional functions. Let's take a look at this picture!

 

2. Advantages: A. Trigger is automatically executed; B. The stored procedure can be called; c. The data condition constraints can be enhanced; D. The data can be disabled or rolled back.

Changes that violate the integrity of the reference; E, cascade, parallel operation; D, multiple triggers in the same table.

3. Category: A, DML (data manipulation language, data manipulation language) Trigger.

Called, including insert, update, delete, and alter.

B. dll (Dynamic Link Library) triggers are caused by data definition events, including: Create

, Alter and drop statements.

 

2. Implement the trigger:

 

1. Create a and MDL triggers.

 

T-SQL syntax:

Create trigger [Schema_name.] trigger_name on {table | view}

[With <dml_trigger_option> [,...... N]

{For | after | instead of} {[insert], [update], [delete]} [with APPEND]

[Not for replication] As SQL _statement [,...... N]

Syntax description: name of the architecture of the Schema_name DML trigger; trigger_name trigger name;

<Dml_trigger_option> parameter, encryption, and execute as options; with APPEND

Specify that an existing trigger should be added. Not for replication, indicating that

And triggers should not be executed.

 

B. dll triggers

 

T-SQL syntax:

Create trigger_name on {allserver | database}

[With <dll_trigger_option> [,...... N]

{For | after} {event_type | event_group }[,...... N]

As SQL _statement [,...... N]

Description: all_server | database, DDL trigger response range, current server or current database.

Event_type | event_group, the name of the T-SQL language event or the name of the event group.

 

 

2. View: A. view the text information of the trigger sp_helptext.

B. view the correlation information of the trigger sp_depends

C. General information: sp_help

The view trigger here is exactly the same as the view stored procedure in the previous blog, because it is a special stored procedure.

 

 

3. Manage triggers

 

1. Modify: to modify the two triggers, you can change "CREATE" in the syntax of "CREATE" to "alter.

2. Delete: Drop trigger

3. Enable and disable: the alter table statement is used to enable and disable triggers.

Syntax: alter table table {enable | disable} trigger, where enable is enabled, disable is disabled

4. Use nested triggers: Use sp_configure

Syntax: sp_configure [[@ configname =] 'name] [, [@ configvalue =] 'value']

Description: @ configname: name of the configuration option.

@ Configvalue: Nesting is prohibited when the value is 0, and 1 indicates Nesting is allowed.

5. Create a column-Level Trigger: Use update to create

Syntax: If Update (colum1) and | or update (column2)]

6. Recursive trigger: call itself as a recursive trigger. Direct and indirect recursion.

 

Of course, these operations can be directly set through microsoftsql Server Management studio. They are the same as many things in the stored procedure, and we can learn a lot through practice.

 

Trigger is a relatively advanced application in the database. flexible use of the trigger can greatly enhance the robustness of the application, database recoverability and database manageability. Of course, as long as we can use it flexibly, we can use it to implement some complex functions, simplify development steps, reduce development costs, increase development efficiency, and improve database reliability.