Some time ago, a friend in the Forum asked me to write a trigger for him. It may be my personal prejudice against the trigger, and I always feel that the trigger is not a good thing. For more than a year of project development, I don't need triggers as long as there is a little logic. It is difficult to maintain data. Program Troubleshooting is also a problem. If you unfortunately still cascade triggers, troubleshooting is even more difficult. So that you don't need it for a long time, and some features of the trigger gradually fade away...
1. Trigger category.
Based on the data operation language, triggers are divided into DML triggers and DDL triggers. DDL triggers are newly introduced since subsql 2005. The specific difference between the two is that the DML trigger is used to respond to delete, update, and insert statements, and the DDL trigger is used to respond to any statement that modifies the database structure, such as create, alter, and drop. Today we will only discuss DML triggers.
Based on the penalty time of the trigger, the trigger is divided into "post-Trigger" and "Alternative trigger ". The trigger is the SQL statement triggered after the delete, update, and insert statements are run. The alternative trigger is that when a delete, update, or insert Statement is issued, the database does not execute the delete, update, or insert statement, directly execute the SQL statement in the trigger; performance analysis should be superior to the alternative trigger. (In any case, the trigger must execute the delete, update, or insert action, and then execute the SQL statement in the trigger, while the alternative trigger directly executes the SQL statement ).
Based on the penalty action of the trigger, the trigger can be divided into insert trigger and delete trigger. The update trigger can be seen as a combination of the insert trigger and the delete trigger.
2. Relationship between the trigger and the transaction. In SQL, you can use BEGIN Tran... commit Tran to explicitly specify a transaction. However, SQL treats updatet, delete, or insert statements as "implicit transactions. The trigger statement is encapsulated in a recessive transaction. Therefore, you can use rollback to roll back the transaction in the trigger without having to worry about in Tran. When the trigger fails to be executed, the corresponding update, delete, and insert operations also fail.
3. How many times does a trigger occur? When an SQL statement only affects one piece of data, it is obvious that the trigger will be triggered once. But remember: when one SQL statement does not affect one row of data, the trigger will be triggered once. When an SQL statement affects multiple rows at the same time, the trigger can only be triggered once (for example, when the update statement updates multiple rows )! Of course, you can use an insert, update, or delete statement in a loop to ensure that the trigger will be triggered once every time a piece of data is affected.
4. at the beginning of the trigger, you can read the global variable @ rowcount to determine whether the statement after the trigger will continue to be executed. When the value of @ rowcount is greater than 1, you can write a cursor in the trigger so that the trigger can process your data correctly.
5. After the trigger is triggered, when all the statements in the trigger are not executed, the data in the table where the trigger is read is "Dirty read ".
6. When a trigger is used to insert, modify, or delete data to the table of the trigger, Do not worry that the trigger will be triggered recursively. For example, there is an insert trigger on Table A, and the trigger contains insert into (...) values (...) statement. When the insert statement in the trigger is executed, the trigger will not be triggered again. Of course, you can change the database option recursive triggers to true to enable the recursion of the trigger, and it will never cause an endless loop, because the trigger can only be recursion for up to 32 times, more than this number of times, when the last trigger fails, the system rolls back all the modified data that was initially triggered by the trigger.
7. When you really need the trigger cascade or recursion, Please carefully construct your trigger to ensure that the trigger can be properly terminated.
8. when a table has multiple post triggers, and you have requirements on the trigger sequence of the post triggers, you can specify "first Delete trigger", "Last Delete trigger", "first update trigger", "Last Update trigger", and "first Insert" through the system's built-in process "sp_settriggerorder "trigger "" Last insert trigger ". For example, there are four insert triggers in a table. You can specify which insert trigger is triggered first and which insert trigger is triggered last. The penalty sequence of the remaining two insert triggers can only be random. (Note: The trigger sequence cannot be specified for the alternative trigger)
9. You can use the disable trigger clause of the alter table statement to disable the trigger temporarily until you re-enable the trigger.
10. There are two special tables: inserted and deleted. The two tables respectively store the pre-image and post-image of the data. You can read the data in the inserted Table to know what data will be inserted into the table. You can read the data in the deleted table to know what data is about to be deleted.
Trigger, there are so many issues to pay attention...
