Trigger, thing

Tag: Value copy disk -- action prediction specified job Deletion

Trigger:

A trigger is a special type of stored procedure that automatically takes effect when a language event is executed. SQL Server includes three common types of triggers: DML triggers, DDL triggers, and logon triggers.

When a Data Definition Language (DDL) event occurs on the server or database, the DDL trigger is called. The login trigger will trigger the Stored Procedure in response to the logon event. This event is triggered when a user session is established with the SQL server instance.

When a data operation language (DML) event occurs in the database, the DML trigger is called. DML events include insert statements, update statements, or delete statements for modifying data in a specified table or view. DML triggers can query other tables and contain complex Transact-SQL statements. Treat the trigger and the statement that triggers it as a single transaction that can be rolled back within the trigger. If an error is detected (for example, insufficient disk space), the entire transaction is automatically rolled back.

DML triggers include the after (for) and instead of triggers. DML triggers use the deleted and inserted logical (concept) tables. They are structured like tables that define triggers, that is, tables that attempt to perform user operations on them. In the deleted and inserted tables, the old or new values of rows that may be changed by the user are saved.

For insert operations, inserted retains new records, and deleted has no records

For delete operations, inserted has no records, and deleted retains the deleted records.

For the update operation, inserted retains the modified record and deleted retains the record before the modification.

Example 1: Create and execute a delete trigger using the instead

Example 2: delete a piece of data and use deleted to indicate the deleted data.

 

Example 3: insert adds a data entry. Inserted indicates the newly added data, and obtains the instructor ID from the inserted data. The instructor ID indicates that the newly added data is male and female.

 

Example 4: When you forget the trigger name, use the following statement to disable or enable the trigger.

 

Database transactions:

Database Transaction refers to a series of operations performed as a single logical unit of work.

Transaction Processing ensures that data-oriented resources are not updated permanently unless all operations in the transaction unit are successfully completed.

 

Imagine a transaction for online shopping. the payment process includes at least the following database operations:

Update the inventory information of the products purchased by the customer

Save customer payment information-may include interaction with the banking system

Generate an order and save it to the database. · update user information, such as the number of purchased items. Normally, these operations will proceed smoothly and the transaction will be successful, all database information related to transactions is also updated successfully. However, if an error occurs in any part of the process, for example, an exception occurs when updating the inventory information, or the customer's bank account has insufficient deposits, the transaction will fail. Once a transaction fails, all the information in the database must remain unchanged before the transaction. For example, if the last step fails to update the user information, the transaction fails, make sure that the failed transaction does not affect the database status-the inventory information is not updated, the user has not paid, and the order has not been generated. Otherwise, the database information will be messy and unpredictable.

Database transactions are a technology used to ensure the stability and predictability of transactions in such circumstances.

 

Begin Tran (or transaction) -- start the transaction

Commit -- submit a transaction

Rollback -- roll back the transaction

Transaction Features:

Atomicity)

Consistency)

Isolation)

D durability)

@ Error is a condition for judging whether a transaction has an error. The error-free value is 0, and the error value is not 0.

 

Transaction creation and use example:

Select * From cangkubegin tran -- start the transaction insert into Cangku values (10008, 'boxster', 1004) -- no error. If @ error> 0begin -- this statement is written after each execution statement to prevent the execution of the previous statement regardless of the number of execution statements, go directly to tranrollback goto tranrollback -- To the tranrollbackendinsert into Cangku values (10002, 'aurora ', 66.50,) of the last statement executed) -- primary key constraint if @ error> 0 goto tranrollbackinsert into Cangku values (10009, 'xf', 1003,) -- no error if @ error> 0 begin tranrollback: -- The colon rollback Tran must be added to roll back the commands executed in all transactions (cancel all execution statements) endelsebegin commit tran -- commit a transaction (only when the actual transaction reaches commit is used to change the database data) End

Trigger, thing