A trigger is a special type of stored procedure, which is different from a common stored procedure. A trigger is executed when an event is triggered, and a stored procedure can be directly called by the stored procedure name. When a record is inserted, modified, or deleted in a table, SQL Server automatically runs the SQL statement defined by the trigger to ensure data integrity. Many times we have such a requirement that the information of a category belongs to another big category. to delete a large category, the following small categories should also be deleted, in addition, the primary key and foreign key are set between tables. For example, there are three representations: News classification table, news table, and comment table. They are a one-to-many relationship. If you delete a news category, the news and comments of each news category should be deleted, if a foreign key is set up between the three tables, an error occurs when you delete the category table or news table. The trigger can be used to solve this problem. Therefore, when deleting a news category, you must first Delete the following news, that is, the records in the News table under this category. The code for creating a trigger is as follows: In the trigger, pay attention to the content in the red box and the green box in the Code. The red box can be instead of or after, indicates that update, insert, and delete are triggered before or after execution. After: indicates that the trigger is activated only after the specified insert, delete, and update operations are executed, and the SQL statement in the trigger is executed.
Instead of: When an instead of Type trigger for insert, delete, and update operations is defined for a table or view and corresponding operations are executed, even if the trigger is triggered, however, the corresponding operation is not executed, but only the trigger SQL statement itself is run. This statement indicates that if it is an instead of Type trigger, the executed SQL statement itself will not be executed, but will only execute the content in the trigger. If the cascade deletion between three tables is similar to the deletion between two tables, the difference is that when the records in the third table are deleted, all comments will not belong to only one news class, you can use the "in" keyword to delete the object. As follows: You may not understand deleted in the trigger statement, which involves how the trigger works.
Each trigger has two special tables: inserted and deleted ). These two tables are logical tables managed by the system and stored in the memory rather than in the database. Therefore, users are not allowed to directly modify them. The structure of these two tables is always the same as that of the table to which the trigger is applied. The two tables dynamically reside in the memory. When the trigger completes, the two tables are also deleted. The two tables mainly store the original or new data values affected by user operations. The other two tables are read-only, that is, the user cannot write content to the two tables, however, data in a table can be referenced. A trigger is the same as an event trigger. For example, a click event is an event trigger event that allows multiple associated services and operations to be used flexibly as needed.
