Triggers in SQL Server

last year's contact with the trigger confused, now also has a general understanding. Like the self-taught, learning is really a need to repeat the process, from unfamiliar to familiar with this is a process.

first, the basic concept        

A trigger is a special type of stored procedure that differs from normal stored procedures. Triggers are executed primarily by triggering events, and stored procedures can be called directly by the stored procedure name. When you insert a record, modify a record, or delete a record in a table, SQL SERVER automatically executes the SQL statement defined by the trigger to ensure the integrity of the data. second, the role as I understand it, the main function of triggers is to ensure the integrity of the data. ① It can be cascaded to modify related tables in the database. such as student recharge, update the top-up list, the balance information in the card table should also be updated. The ② performs complex constraint operations. You can write complex T-SQL statements in triggers and use statements such as if...else to make complex judgments. ③ Track Changes and do rollback processing for non-conforming conditions. For example, if you want to specify that the change in salary should not exceed 40%, use a trigger, you can compare the modified table data with the table data before the modification, if more than 40%, you can roll back the modification operation. ④ calls the stored procedure. In response to database updates, triggers can invoke one or more stored procedures. third, shortcomings the performance of a trigger is usually low. When we delete a table or insert a table that is always in memory, and the reference table in the execution of the trigger is neither in memory nor on the database device, it takes longer to execute. Iv. TypesUpdate Trigger: Triggered when an update operation is made on a table. Insert trigger: Triggered when an insert operation is made on a table Delete trigger: triggered when a delete operation is made on a table Instead of triggers: Instead of triggers are triggered when an insert, update, or delete operation is not performed.

after trigger: Fires after a triggering action occurs, and provides a mechanism to control the order in which multiple triggers are executed.

V. Use of triggers
① Create

Create TRIGGER trigger_name--The name of the trigger on Table|view                          --The name of the table associated with the user-created trigger {for | After | INSTEAD of}      --Indicates which data operation will activate the trigger {[Insert][,][update][,][delete]}as sql_statements                           --The SQL statement to execute

② Modification

ALTER TRIGGER trigger_name--The name of the trigger on Table|view                          --The name of the table associated with the user-created trigger {for | After | INSTEAD of}      --Indicates which data operation will activate the trigger {[Insert][,][update][,][delete]}as sql_statements                           --The SQL statement to execute

Note: Create in the statement that created the trigger is changed to alter, and the remainder is identical. However, the trigger name referenced in ALTER TRIGGER must be a trigger name that already exists

③ Delete

Drop trigger Trigger_name [,... N]

Here is a small example of a refactoring room that updates the balance in the card table while updating the top-up record table.

ALTER trigger [dbo]. [Tr_updatemoney]on [dbo]. [T_recharge] After Insertas declare @addmoney decimal (18,2), @card_id varchar (11)--declaring variable Select @addmoney =addmoney, @card_id =card_id< C2/>from inserted--inserted is the system automatically created temporary table update t_card set money [email Protected]+money where card_id [email protected]_ id--Executing SQL statements