Author:
Native
Original address: http://blog.9cbs.net/shoutor/archive/2004/07/21/47721.aspx
Keywords: Trigger instance In the database design, there are two ways to automate data processing rules. One is the condition constraint, one is a trigger, in general, the conditional constraint is easier than the trigger and Maintenance, and perform efficiency, but the conditional constraint can only be used to make a simple field queue, and when it comes to complex operations such as multitario operation, it is necessary to use the trigger. One database system has two virtual The table is used to store information recorded in the table, namely: Virtual Table INSERTED, Virtual Table DELETED Store new records to update new records to update new records to update new records When deleting, the type of record trigger that is stored is not stored and the Trigger Trigger After Trigger: The trigger timing will make the necessary good and post-processing of the change data after the data has changed, and if there is an error, transactions Rollback Transaction Replys all the information fifted by this operation. Interted Of Trigger: Trigger the time before the information changes, and how the information changes depend on the trigger. Now introduce the format of the creation of the trigger: AFTER Type: CREATE TRIGGER Trigger Name ON Name After AFTER AS SQL Statement Instead Of Type: CREATE TRIGGER Trigger Name ON Name INSTEAD OF A Operation (Update, DELETE) ASSQL Statement Instance 1: In Order (Table The order quantity in the order (column name NUM) changes, the trigger will first go to the customer (Table Customer) to acquire the user's credit rating (column name Level), and then remove it to the credit amount The order quantity of the order license is up and lower, and the order quantity in the order is compliant. Code: Create Trigger Num_Checkon ORDERSAFTER INSERT, UPDATEAFTER INSERT, UPDATEAFTER INSERT (NUM) Begin IF EXISTS (SELECT A. * From ORDERS A JOIN CUSTOMER B On A.CUSTOMERID = B.CUSTOMERID JOIN Creit C on B.LVEL = C.LEVEL WHERE A. Num Between C.up and c.down) Begin Rollback Transaction Exec master..xp_sendmail 'Administrator', 'customer's order quantity does not meet the limit' End End instance 2: There is a salary management system, when the company's monthly salary of a employee When adjusting, the salary column will usually be modified in the table employee, then modify the salary adjustment time and salary CREATE TRIGGER Compensationon employee after the table employee record. No., paydate () from insertedend
