Steps, methods and skills of using triggers in SQL server

How to use triggers in SQL server

What is a trigger

When inserting, updating, and deleting one table, it will trigger the inserting, updating, and deleting of another table

Why triggers are needed

Insertion, update, and deletion of tables are sensitive operations. We hope to record the information of these operations in another table, so that we can clearly know which operations were performed in the future

How to use triggers

The first step is to determine the type of trigger

AFTER	The trigger is triggered after the trigger operation (INSERT, UPDATE or DELETE)
INSTEAD OF	Instead of a trigger, the operation (INSERT, UPDATE or DELETE) is not actually executed

For example, there is a buy table (buyid, name, cost), and now you want to insert a piece of data after into the buy, and add a new one in the detail table (detailid, buyid, name, kind, oldcost, newcost) Corresponding record

First of all, we want to insert this record into the buy, and add a new record on the detail after this operation, so we use AFTER; secondly, we want the INSERT operation triggers the operation of adding a new record on the detail, so it can be expressed as AFTER INSERT; finally, to sum up the above, you can write the statement:

CREATE TRIGGER tri_buy_insert on buy
AFTER INSERT
AS
BEGIN
New operations on detail...
END

Now, we want to create a delete trigger on the buy table, so that when trying to delete a piece of data on the buy, do not execute the delete, but add a corresponding delete record in the detail table

First of all, we don’t want to perform the delete operation, so we need to use the alternative trigger INSTEAD OF; secondly, we want the DELETE operation on the buy table to be triggered Add an operation to the detail table, so it can be expressed as INSTEAD OF DELETE; finally, to sum up the above, you can write the statement:

CREATE TRIGGER tri_buy_delete on buy
INSTEAD OF DELETE
AS
BEGIN
New operations on detail...
END

It can be seen that the framework for creating triggers is basically the same:

CREATE TRIGGER 1. Trigger name on 2. Table name
3. Determine the trigger type
AS
BEGIN
4. New operations on detail...
END

You only need to complete 1, 2, 3, and 4 respectively

Among them, 1 and 2 are very simple. The method of determining the trigger type in 3 has been explained. Let’s talk about how to determine 4

The second step is the operation after the trigger

Insert operation

First, one way of writing the INSERT statement is:

INSERT INTO 1. Table name
SELECT...
FROM 2. Table name

1. Table name refers to the table in which the record is inserted

2. Table name refers to getting data from that table

Still the example just now, there is a buy table (buyid, name, cost), now I want to insert a piece of data after into the buy table, in the detail table (detailid, buyid, name, kind, oldcost, newcost) Add a corresponding record

First make sure that the table that needs to insert records is a detail table

Then make sure to get the data from the record just inserted in the buy table

So, you can write:

INSERT INTO detail
SELECT...
The record just inserted on FROM buy

Now the question is, how to express the record just inserted on buy?

In fact, if our operation (INSERT, DELETE, UPDATE) has a trigger, it will create a temporary table for us, where the data of the insertion operation will be stored in the inserted record; and the deletion operation The data will be stored in the deleted record; the update operation data can be regarded as doing DELETE first, and then doing INSERT, so the old data of the update operation is stored in deleted, and the new Data is stored in inserted.

So, we can write:

INSERT INTO detail
SELECT...
FROM insert i

Then, the statement after SELECT has not been written yet, just write it according to the fields of the detail table:

INSERT INTO detail(buyid,name,kind,oldcost,newcost)
SELECT i.buyid, i.name, "New", 0, i.cost
FROM insert i --Indicates the line of record inserted into the buy

Combined with our trigger framework is:

CREATE TRIGGER tri_buy_insert on buy
AFTER INSERT -- 1. Determine the type
AS
BEGIN
    -- 2. Operation after trigger
    INSERT INTO detail(buyid,name,kind,oldcost,newcost)
    SELECT i.buyid, i.name, "New", 0, i.cost
    FROM insert i --Indicates the line of record inserted into the buy
END

Let’s take a look at the execution process:

First, after inserting data into the buy table, fire the trigger and execute the operation after the trigger.

Then, in the triggered operation, execute FROM insert i first, obtain the record inserted into the buy line, and take the alias i.

Then, execute the SELECT i.buyid, i.name, “new”, 0, i.cost operation to construct the projection of the i record into what we need

Finally, insert the constructed record into the detail table. Since the detailid in detail is self-incrementing, we don’t need to specify

Delete operation

In the second example just now, we want to create a delete trigger on the buy table, so that when trying to delete a piece of data on the buy, do not execute the delete, but add a new one in the detail table Delete the record accordingly.

CREATE TRIGGER tri_buy_insert on buy
INSTEAD OF DELETE -- 1. Determine the type
AS
BEGIN
    -- 2. Operation after trigger
    INSERT INTO detail(buyid,name,kind,oldcost,newcost)
    SELECT d.buyid, d.name, "Delete", d.cost, 0
    FROM deleted d -- Indicates the row to be deleted on the buy table
END

The execution steps are similar to the insert operation. First, when trying to delete a row of data in the buy table, activate the trigger and execute the triggered operation. Then, first take the data from FROM, project it into SELECT, and finally insert it into the detail table. The biggest difference is that the trigger type used by the delete operation is the INSTEAD OF trigger, which will not The one that actually performs the delete operation, that is to say, the record to be deleted is still on the buy table

Update operation

Create an update trigger so that after updating a piece of data on buy, a corresponding record is added to the detail table

CREATE TRIGGER tri_buy_update on buy
AFTER UPDATE
AS
INSERT INTO detail(buyid, name, kind, oldcost, newcost)
SELECT d.buyid, i.name, 'update', d.cost, i.cost
FROM inserted i, deleted d

It is similar to adding and deleting operations, very simple.

The judgment statement in the triggered operation

There is a statistical table calc(maxcost, mincost, totalcost, avgcost), and now I want to create an insert trigger on the buy table. If the clac table is currently empty, when trying to insert a piece of data on the buy, it will insert a statistical record on the calc. If the calc table currently has data, then when trying to insert a piece of data on the buy, update the statistics record on the calc.

CREATE TRIGGER tri_buy_insert1 on buy
AFTER INSERT
AS
IF NOT EXISTS( SELECT * FROM calc ) -- If the calc table does not exist, add it
BEGIN
INSERT INTO calc
SELECT MAX(b.cost), MIN(b.cost), SUM(b.cost), AVG(b.cost)
FROM buy b
END
ELSE -- otherwise, that is, the calc table exists, update it
BEGIN
DELETE FROM calc
INSERT INTO calc
SELECT MAX(b.cost), MIN(b.cost), SUM(b.cost), AVG(b.cost)
FROM buy b
END

This example differs significantly from the statements in the other examples.

First of all, this example has a judgment statement, which means that the operation after the trigger is executed with a judgment statement, which can be more flexible

Secondly, the objects of FROM are no longer inserted and deleted. This is because the aggregation functions MAX, MIN, etc. obviously have to obtain the data of the entire buy table to draw conclusions, and inserted and deleted only represent the row being operated. That’s all.

Finally, we hope that the calc table exists to update the calc table, but in fact we delete it first, and then reinsert it in two steps. Why do we do this? This is because the UPDATE statement does not use aggregate functions. I just This method can be used instead.

Summary

This article is mainly divided into two parts
The first part is to determine the type of trigger, write a basic framework of a trigger, as long as you can write this framework silently, you can easily write a trigger
The second part is the operation after the trigger. The operation after the trigger can be INSERT, DELETE, UPDATE. The steps to be executed are generally to take out the data from the FROM first, then project it into the SELECT, and finally complete the INSERT, UPDATE operations, etc.