Simple inbound and simple outbound triggers

1. Trigger background

After completing the individual restructuring of the data room charging system, I only used the storage process. Recently, I was reading a video about the use of triggers. At that time, I encountered the problem that foreign key constraints cannot be deleted or associated table records, some of the solutions at that time used stored procedures, or marked associated constraint keys unavailable. In fact, triggers are similar to stored procedures. It can be said that triggers are a special type of stored procedures.

Here is an example of a data center charging system:

Intercept a part of the database relationship diagram:

If you want to delete a user, the user's recharge and return records will be affected. In this case, the foreign key constraint error occurs.

This is because the recharge and return tables are related to the user ID attribute of the User table. When deleting a user in the User table, you should delete the user ID record in the recharge and return tables together. In this way, Chu Xiang will not make such a mistake. When we need to delete all the cascade tables of a table, the trigger will pop up.

2. trigger application

Taking the news publishing system as an example, the database design is as follows:

When the drug deletes a news category, there are related news under the news category, and there are many news comments under each news. When deleting a news category, we need to delete the news of this ID in the News table and delete the idnewsid in the comment table as the comment of this news table.

The trigger is as follows:

Create a storage process in the news category: because it is to delete the news category, that is, to delete is the trigger point.

-- ===================================================== ====== -- Author: xu Dan -- create Date: 2014-7-18 -- Description: delete A category trigger -- ========================================== ========= alter trigger [DBO]. [trigcategorydelete] -- trigger name on [DBO]. [category] -- operate on the database table instead of Delete -- replace the delete statement as begin with the following statement -- delete all news under this category -- delete news where caid in (select ID from deleted) declare @ caid intselect @ caid = ID from deleted -- Delete comment where newsid = (select newsid from news where caid [email protected]) -- delete news Delete news where caid [email protected] -- first delete all news under this category -- delete category where [email protected] -- Then delete this category end

Explanation of the three deletion statements:

First, declare a @ ID variable. When you delete a news category

delete from category where id=6

This is a contact. This trigger is triggered instead of directly deleting the category. Replace the delete operation with the delete statement under the trigger. Select the ID of the deleted news category, delete all comments under the news ID, delete all news under the category, and delete the category. This solves the effect of cascading deletion.

In fact, the trigger is similar to the creation of a stored procedure and has its specific statements. The following is a brief introduction to my understanding of the trigger:

3. Trigger concept:

A trigger is a special type of stored procedure. When adding, deleting, or modifying a table or view, the trigger is triggered to start running. Sometimes, the trigger can also be interpreted as an automatically executed stored procedure. Instant messaging.

4. Advantages of triggers:

• The trigger is automatically executed.
• Triggers can be used to cascade changes to relevant tables in the database. A trigger is based on a table, but it can perform multi-Table operations and cascade related tables.
• Triggers can implement data integrity constraints that are more complex than check constraints.
• The trigger can evaluate the status of the table before and after data modification and take countermeasures based on the difference.
• A table can have three triggers (insert, update, and delete) for different operations at the same time. There may be multiple corresponding countermeasures for the same modification statement.

5Use the trigger with caution

The trigger has powerful functions and can easily and reliably implement many complex functions. Why should we use it with caution. Trigger itself is not at fault, but because of our misuse, it will cause difficulties in database and application maintenance. In database operations, we can perform data operations through relationships, triggers, stored procedures, and applications ...... Meanwhile, rules, constraints, and default values are also an important guarantee for data integrity. If we rely too much on triggers, it will inevitably affect the database structure and increase the complexity of maintenance.

Summary:

Watching niujiao videos has a sense of mutual sorrow. Humans are really powerful, making our programming language simpler and more powerful, and implementing more and more powerful functions, yesterday I heard about the experience of data center cooperation. I mentioned the strength of EA. The database design is getting simpler and simpler. EA can generate code and export the database, at that time, I felt that my knowledge was really lacking, and many did not try it. I used some stupid methods. The same is true for triggering. It would be too troublesome for a cascade table to have one hundred (of course, this database is redundant and there should be no such database. Programming is not over yet. To achieve better and more efficient, a simpler solution is our effort.