SQL---Triggers

First, what is a trigger?

A piece of SQL code, hanging to a table of some increment, delete, change the operation.

The corresponding SQL code is triggered when the table performs the appropriate operation .

the difference between a trigger and a stored procedure :

1. The stored procedure is independent of the table, and triggers need to be attached to an action on a table.

2. The stored procedure needs to be invoked with a name to execute, and the trigger is automatically triggered during the operation of the table.

Second, the classification of triggers:

After triggers

--after the operation of adding and deleting the table, trigger the trigger again.

Instead of triggers

--Do not perform additions or deletions to the table, and it is only capable of triggering triggers.

III. syntax for creating triggers

Create TRIGGER Trigger name on table name After/instead of Insert/delete/update

As

Go

Iv. two temporary tables in a trigger: inserted,deleted

These two tables are temporary tables, and when the trigger executes, it will automatically disappear and the trigger will be created again.

The structure of the two tables is the same as the structure of the table behind on (column name, number of columns, type). And there's only one record in it.

Insert operation--put the new data into the inserted table.

Delete operation--put the deleted data into the deleted table.

Modify--Put the old data into the deleted table and put the new data in the inserted table.

V. Use of two temporary tables.

Remove the data from the two temporary tables and put them in the variable for later use.

Case one: Make a car change chart, the increase of the car, after the deletion of the change table added one. Implemented with triggers. After Trigger:

Code:

-- Create a trigger (auto-start trigger after car increase)

1 Delete  fromCarwhereCode='c003' 2 3 Create TriggerTr_car_insert onCar afterInsert4 5  as6 7 --Step One: Take the name and price of the inserted data out and put it in two variables8 9 Declare @name varchar( -),@price decimal(8,2)Ten  One Select @name=Name@price=Price frominserted A  - --Step Two: Insert the above two variables into the Cardelete table -  the Insert  intoCardeleteValues(@name,@price)

-- Create a trigger (delete to start trigger)

1 Create TriggerTr_car_delete onCar afterDelete2 3  as4 5 --The trigger will have two temporary tables at run time, and the name of the table is fixed, respectively, called inserted,deleted.6 7 --Step One: Remove the name and price of the data you just deleted and put it in two variables8 9 Declare @name varchar( -),@price decimal(8,2)Ten  One Select @name=Name@price=Price fromdeleted A  - --Step Two: Insert the above two variables into the Cardelete table -  the Insert  intoCardeleteValues(@name,@price) -  - Go -  + Go

Case TWO: Delete the info data and delete the corresponding data from the work and family two tables using a trigger before deleting it . Instead of triggers

Code:

Delete  fromInfowhereCode='p003'Alter TriggerTr_info_delete onInfo instead of Delete as--get the primary key value from the temporary tableDeclare @code varchar( -)Select @code=Code fromdeleted--Remove the above value from the Infocode column in workDelete  from  Work whereInfocode=@code--Remove the above value from the Infocode column in familyDelete  fromFamilywhereInfocode=@code--Remove the value above in code from infoDelete  fromInfowhereCode=@codeGo

SQL---Triggers