Database background Optimization-triggers

To ensure database integrity and consistency, You need to execute multiple SQL statements to achieve the desired purpose.

In a one-to-many database relationship, such as the relationship between the card number category and card number. If you want to cancel a category, you must delete all the cards under the same category. In reality, it is generally used for recharge during registration. When I want to write something in the card table, I need to write a recharge record. The two operations are deemed to have completed the implementation of this function.

For the above requirements, the implementation of each function is accompanied by the execution of Multiple SQL statements.

There are two solutions to this problem. One is the stored procedure, and the other is the trigger. The storage process has been explained in the previous blog. So this blog will certainly explain the trigger.

 

Trigger: a series of operations performed after an operation occurs.

In addition to queries, the database only has three operations: add, delete, and modify. Therefore, triggers are divided into three types: insert, delete, and update.

 

Let's take a look at the example below:

Here are the two tables in the database I created: (T_card and T_type, and the two tables are configured with primary key and foreign key constraints)

 

The following is the result of deleting a temporary user. (This is caused by foreign key constraints)

 

Then I will create a delete trigger to check whether the trigger can be implemented.

Create trigger delType ON T_type AFTER deleteAS BEGINdeclare @ typeId intdelete T_card where typeid in (select typeid from deleted) -- retrieve typeIdENDGO from the deleted table

 

The execution result is the same as the preceding error.

 

Let's take a look at the definition template for creating a trigger in SQL server:

We have noticed that the alter statement does not exist. The insert delete update trigger is a series of SQL statements that are executed after the operation is completed. If the trigger's conditional execution fails, no subsequent operations can be performed.

 

Therefore, everyone is thinking. If I delete the card number of the 'temporary user' first and delete the card number category, will the error disappear.

 

You need to find a series of operations to replace the delete method.

Alter TRIGGER delType ON T_type instead of deleteAS BEGINdeclare @ typeId intselect @ typeId = typeid from deleted -- retrieve typeId from the table to be deleted: delete T_card where typeid in (select typeid from deleted) has not been deleted -- delete the card number delete T_type where <a target = _ blank href = "mailto: typeid = @ typeid "> typeid = @ typeid </a> -- delete the ID class ENDGO

 

Then execute the SQL statement to check the effect:

 

Achieve the desired effect. Deleted the temporary user category and the temporary user's card number.

 

Trigger requirements: when adding, deleting, or modifying a table, you need to perform a series of operations on other tables.

 

Use triggers to ensure database integrity. Write stored procedures and triggers. It can reduce program code and difficulty. It also aims to reduce repeated code. If a system does not use triggers, stored procedures, or other methods. The amount of code repetition will be terrible. On the contrary, if a database is designed and stored procedures and triggers are written. It's much simpler to look at this system.