Trigger 1 _ features of a conceptual trigger: 1. A trigger is a stored procedure that is automatically executed when a table is added, deleted, or modified. A trigger is often used to force business rules. It is an advanced constraint that is triggered by an event and executed. 2. A trigger is a special transaction unit. It can reference columns in other tables to execute special business rules or data.
Trigger 1 _ features of a conceptual trigger: 1. A trigger is a stored procedure that is automatically executed when a table is added, deleted, or modified. A trigger is often used to force business rules. It is an advanced constraint that is triggered by an event and executed. 2. A trigger is a special transaction unit. It can reference columns in other tables to execute special business rules or data.
Trigger 1 _ Concept
Trigger features:
1. A trigger is a stored procedure that is automatically executed when a table is added, deleted, or modified. A trigger is often used to force business rules. It is an advanced constraint that is triggered by an event and executed.
2. A trigger is a special transaction unit. The U.S. space can reference columns in other tables to execute special business rules or data logical relationships. When an error occurs, you can perform the rollback transaction operation to roll back the entire trigger along with the T-SQL statement that triggers it (without displaying the Declaration begin transaction ).
3. Two temporary tables used by each trigger:
Deleted temporary table: used to temporarily store copies of deleted record rows (including data rows affected by the delete and update statements );
Note: The deleted record rows are first deleted from the original table and saved to the trigger table. Delete the table from the trigger table and save it to the deleted table.
Inserted temporary table: used to temporarily store the inserted copy of record rows (including the data rows affected by the insert and update statements );
Features of the deleted and inserted tables:
> The table structures of the two tables are the same as those of the trigger;
> These two tables are logical tables and are managed by the system;
> These two tables dynamically reside in the memory (not stored in the database), and are also deleted after the trigger is completed;
> These two tables are read-only, that is, they can only be viewed using the select statement (you cannot directly change them );
4. The created trigger (insert, delete, and update) is triggered after the data row in the original table has been modified. Therefore, the trigger is executed only after the constraint check.
When should I use a trigger?
A. The complex reference integrity and data consistency that cannot be guaranteed by the primary-foreign key relationship.
However, U.S. servers can more effectively execute these changes through cascading integrity constraints.
B. prevent malicious or incorrect INSERT, UPDATE, and DELETE operations, and enforce other restrictions that are more complex than the Restrictions defined by the CHECK constraint.
> Unlike CHECK constraints, DML triggers can reference columns in other tables;
> The trigger can complete all the constraints on the VM, but not necessarily the best solution;
> Triggers can use custom information and complicated error handling;
C. DML triggers can evaluate the status of tables before and after data modification, and take measures based on the difference.
D. the DML trigger for the same modification statement in a table can be responded by multiple different operations (INSERT, UPDATE, or DELETE;
Trigger type:
Insert trigger; (omitted)
Delete trigger; (omitted)
Update trigger: This trigger is triggered when the record row or column data in the table is modified;
Note: update (column) function: checks whether a column is modified.
The update operation is divided into two steps:
First, "delete" the original data row before modification: the original deleted Data row is copied to the deleted temporary table;
Then, insert the new data row after the change: Insert the new data row to the original table, and save the new data row to the inserted temporary table and trigger table;
Note:
1. create trigger must be the first statement of the batch processing (go;
2. A trigger statement can only be used in one table or view;
On Table Name/view name
3. One trigger statement can execute multiple operations;
For delete, insert, and update -- no sequential combination
4. We recommend that DML triggers do not return any results. This is because the special processing of these returned results must be written to each application that allows modification to the trigger table.
To prevent any results from being returned from the DML trigger, do not include the select statement or variable assignment in the trigger definition;
If you must assign values to variables in the trigger, you should use the set nocount on statement before the trigger is triggered to avoid returning any result set;
Note: in future versions of SQL Server, the function of returning the result set from the trigger will be deleted.
5. If the "trigger table" also has constraints, the constraints on the "trigger table" will be checked before the insert, delete, and update triggers are executed. If the constraints are not met, the insert, delete, and update triggers are not executed.
View All triggers in the current database
