C # Trigger Knowledge summary and case study

Trigger

1. triggers are stored procedures that are automatically executed when a table is inserted, updated, deleted, and often used to enforce business rules, an advanced constraint that can define more complex constraints than a check constraint. Can execute complex SQL statements (if/while/case)
2. triggers can be defined on a specific table and are related to a table
3. Automatic Trigger execution
4. cannot be called directly
5. is a transaction ( can be rolled back )
6. Types of Triggers

(1) DELETE trigger

(2) INSERT trigger

(3) UPDATE trigger

1. Inserted table and deleted table

when trigger triggers:

the system automatically creates deleted tables or inserted tables in memory

Deleted tables and inserted tables are read-only and are not allowed to be modified ; automatic deletion after trigger execution completes

Inserted Table

record lines that were temporarily saved before deletion or update

You can check whether the inserted data meets business requirements from the inserted table

if not , an error message is reported to the user and the insert operation is rolled back

Deleted Table

record lines that were temporarily saved before deletion or update

You can check whether the deleted data meets business requirements from the delered table

if not , an error message is reported to the user and the delete operation is rolled back

note : executing insert operation first create inserted table Put the inserted statement in the inserted table If not satisfied, save the error message to the user and rollback insert operation successful execution if satisfied

execute deleted operation first create table Put the deleted statement to deleted Table Determine if the deleted statement meets business requirements If not satisfied, you want users to report error messages successful execution if satisfied

execute update operation first create table and deleted table Add the data in the table you want to modify by adding deleted Span style= "Font-family:calibri",

then put the modified data into inserted Judging if the statement meets business requirements If not satisfied, save the error message to the user and rollback insert operation successful execution if satisfied

Modify Operation	Inserted table	deleted table
Add (INSERT) record	Storage of new records	------
Deleting records (delete)	-----	To store deleted records
Modify (UPDATE) record	Storage of updated records	Store pre-update records

The table below summarizes the following :

1. Create a Trigger

Syntax :

Create trigger Trigger name

On table name

[WITH Encryption]

For[delete/insert/update]

As

T-SQL statements

GO

[WITH Encryption] that represents the encryption trigger definition. SQL text

[Delete/insert/update] specifying the type of trigger

use of 9.insert triggers :

--when inserting a trade information into the Transaction information table (Transinfo),

--We should automatically update the balance of the corresponding account

1. use of the DELETE trigger :

when deleting the transaction information table, it is required to automatically back up the deleted data to the table backuptable .

1. use of UPDATE triggers

In addition to tracking changes (modifications) to the data, the 11.UPDATE trigger can also check if the data for a column has been modified

Use the UPDATE (column) function detects if a column has been modified

Problem:

The trading date is usually generated automatically by the system, which defaults to the current date. For security

, it is generally forbidden to modify to prevent fraud.

The trigger is also a special transaction unit that can be executed when an error occurs . ROLLBACK TRANSACTION rollback undo Operation

triggers typically need to use temporary tables:deleted tables and inserted tables, which hold copies of deleted or inserted record rows

C # Trigger Knowledge summary and case study