SQL Server CREATE Trigger

Why do I need triggers (TRIGGER? )? The typical application is that the bank's ATM system assumes that the system's database design requires two tables: The Account Information Table (Bank) holds the account information, and the Transaction information table (TRANSINFO) stores each transaction information. What is wrong with the above results? You must have found out that when Zhang San took 200 yuan, although the Transaction information table (Transinfo) saved 200 yuan of the transaction information, but the balance of the Account table (bank) is still 1000, does not automatically follow the changes. Obviously, we should automatically reduce or increase the balance in the Account table based on whether the transaction type is "withdraw" or "deposit". Moreover, it should have the characteristics of the transaction: Once the transaction fails, the balance modification should also be automatically canceled. So, how to solve it? Does this special business rule use normal constraint lines? The answer is clearly negative. Do you use transaction lines? Transactions can guarantee that the balance changes are automatically canceled once the transaction fails. However, the triggering function of automatic modification cannot be realized. Therefore, the best solution is to use triggers. Trigger It is a special kind of stored procedure, and it also has the function of transaction, it can execute special business rule or maintain complex data logical relationship between multiple tables. What is a trigger then look at an example: there are currently two tables that store information about both employees and retired employees. Assume that now Zhao two retired: Zhao will be removed from the employee table. Once the Zhao information is deleted, an action should be automatically triggered: Save Zhao's information to the Retired Employees table. Emphasis: automatic triggering, rather than manual, how to implement it, of course, using triggers. Q Triggers are stored procedures that are automatically executed when an INSERT, update, or delete operation is performed on a table the Q trigger is typically used to enforce a business rule Q trigger is an advanced constraint that can define a complex SQL statement that is more complex than a check constraint Q (if/ while/ CaseQ can refer to columns in other tables The Q trigger is defined on a particular table, and the table-related q automatically triggers the execution of q unlike a stored procedure, which does not need and cannot be manually called to execute Q itself is a transaction, so if you find an error, you can roll back the undo For Type of trigger INSERT trigger: The SQL statement defined by the trigger is automatically executed when data is inserted into the table. UPDATE trigger: Fires when an riedo column in a table is updated, automatically executing the SQL statement defined by the trigger.   Delete trigger: Triggered when a record in a table is deleted, automatically executes the SQL statement defined by the trigger.       Inserted and deleted table Q trigger When: Q system automatically created in memory deleted table or inserted table Q read-only, not allowed to modify; Automatically delete Q inserted table Q when trigger execution completes Temporarily save inserted or updated record line Q You can check from the inserted table whether the inserted data meets business requirements Q if not, report an error message to the user and roll back the insert Operation Q deleted table Q temporarily saves the deletion or Record lines before update q you can check whether the deleted data meets the business requirements from the deleted table Q if it is not met, the error message is reported to the user, and the insert operation modification action is rolled back inserted table deleted table increased (INSERTrecord to store new records------DeleteDELETE) Record-----Store deleted record Changes (UPDATEThe record holds the updated record before the record is stored inserted the table and the deleted table holds the information on how to create the trigger syntax for triggers to be created:CREATE TRIGGERtrigger_name ontable_name[With encryption]   for [DELETE, INSERT, UPDATE]  asT-SQL statementsGO withencryption represents the SQL text for the cryptographic trigger definitionDELETE,INSERT, update specifies the type of trigger that the INSERT trigger works on:1. Execute the Insert INSERT statement to insert data rows into the table;2. Trigger an INSERT trigger to insert a backup (copy) of a new row into the system Temp table inserted table3the. Trigger examines the new row data inserted in the inserted table to determine whether it is necessary to roll back or perform other operations. Problem: Solve the above bank withdrawal problem: When a transaction information is inserted into the Transaction information table (Transinfo), we should automatically update the balance of the corresponding account. Analysis: L Create an INSERT trigger on the Transaction information table L get the inserted data row from the inserted temp table l The value of the transaction type (transtype) field is deposited/withdrawal, L increase/reduce the balance of the corresponding account. CREATE TRIGGERTrig_transinfo onTransinfo for INSERT    as   DECLARE @type Char(4),@outMoney  Money   DECLARE @myCardID Char(Ten),@balance  Money   SELECT @type=Transtype,@outMoney=Transmoney,@myCardID=CardID fromInserted/*get the transaction type, transaction amount, etc. from the inserted table*/     IF(@type='withdrawal')/*reduce or increase the balance of the corresponding card number according to the type of transaction*/        UPDATEBankSETCurrentmoney=Currentmoney-@outMoney             WHERECardID=@myCardID    ELSE        UPDATEBankSETCurrentmoney=Currentmoney+@outMoney             WHERECardID=@myCardID... ..GOhow the Delete trigger works:1. Execute the DELETE DELETE statement to delete the data rows from the table;2triggers a delete delete trigger to insert a deleted copy into the deleted table of the system temp table3The . Trigger examines the deleted data in the deleted table to determine whether a rollback or other action is required. Issue: When deleting the transaction information table, it is required to automatically back up the deleted data to the table backuptable. Analysis: l Create a Delete trigger on the Transaction information table l the deleted data can be obtained from the deleted tableCREATE TRIGGERTrig_delete_transinfo onTransinfo for DELETE    as      Print 'start backing up data, please later ...'      IF  not EXISTS(SELECT *  fromsysobjectsWHEREName='backuptable')         SELECT *  intoBackuptable fromDeleted/*get deleted transactions from the deleted table*/     ELSE         INSERT  intoBackuptableSELECT *  fromdeletedPrint 'The backup data is successful and the data in the backup table is:'      SELECT *  frombackuptableGOHow update triggers work: Perform update operations, such as changing the John Doe balance to $20001. The update operation can be seen in two steps:1. Delete John Doe original data: John Doe + 0002  1, back up the data to the deleted table. 2. Insert new Line again: John Doe + 0002  20001, back up the data to the inserted table. Finally, it seems that the balance has been changed from $1 to $20001. So: If we want to see the original data before the change, we can look at the table deleted. If we want to see the modified data, we can view the table inserted. Problem: Track the user's transaction, the transaction amount exceeds 20000 yuan, then cancels the transaction, and gives the error prompt. Analysis: L Create an UPDATE trigger on the Bank table L modified data can be obtained from the deleted table L modified data can be obtained from the inserted tableCREATE TRIGGERTrig_update_bank onBank for UPDATE    as      DECLARE @beforeMoney  Money,@afterMoney  Money /*Get the balance from the deleted table before the transaction, and the balance from the inserted table*/      SELECT @beforeMoney=Currentmoney fromdeletedSELECT @afterMoney=Currentmoney frominsertedIF ABS(@afterMoney-@beforeMoney)>20000 /*whether the transaction amount is >2 million*/        BEGIN            Print 'Transaction amount:'+Convert(varchar(8),                ABS(@afterMoney-@beforeMoney))            RAISERROR('no more than $20,000 per transaction, trade failure', -,1)            ROLLBACK TRANSACTION /*Rollback transaction, undo transaction*/         ENDGOColumn LevelUPDATEthe trigger Q update trigger, in addition to tracking data changes (modifications), can also check whether a column's data is modified by using the update () (column) function to detect if a column problem has been modified: The transaction date is usually generated automatically by the system, and the current date is assumed to be the default. For safety reasons, modification is generally prohibited to prevent fraud. Analysis:UPDATE(column name) function to detect if a column has been modifiedCREATE TRIGGERTrig_update_transinfo onTransinfo for UPDATE    as      IF UPDATE(transdate)/*Check if the transaction date column is modified TransDate*/         BEGIN            Print 'Trading failed .....'            RAISERROR(' Security Warning: cannot be modified during the trading day, automatically generated by the system', 16, 1) ROLLBACK TRANSACTION/* ROLLBACK TRANSACTION, undo transaction * * Endgo OK, let's make a summary: Q Triggers are stored procedures that are automatically executed when an INSERT, update, or delete operation is made to a table, and triggers are typically used to enforce a business rule Q trigger is also a special transaction unit, when an error occurs, you can perform a ROLLBACK transaction rollback undo operation Q Triggers typically require temporary tables: deleted tables and inserted tables, which hold the copy of the deleted or inserted record line Q trigger type: Q Insert trigger Q UPDATE trigger Q Delete Trigger

SQL Server CREATE Trigger