Techniques and examples of trigger design

Trigger | tips | design


trigger design techniques and examples





in database design, there are two methods to set up automatic data processing rules, one is conditional constraint,


is a trigger, generally speaking, conditional constraints are easier to set and maintain than triggers, and the execution efficiency is more


is good, but the condition constraint can only carry on the simple field examination to the data, when involves the multiple table operation and so on complex exercise

When the
is done, the trigger is used.


two virtual tables in a database system are used to store information that changes in a table, respectively


is:


virtual table inserted virtual table deleted





new records when table records are new do not store records


New records to be updated when modified to store records before update


delete records are not stored when the record is deleted


 

The type of
trigger and its trigger time


after trigger: When the data has been changed, it will make the necessary
for the change data.

Rehabilitation and treatment, if found to have errors, then use transaction rollback (Rollback Transaction)


will reply to all the information changed by this operation.


istead of triggers: Trigger timing occurs before data changes, and how the data changes depends on the trigger





now describe the format for creating triggers:





after type:


Create Trigger Trigger Name


on table name


after operation (insert,update)


as


SQL statement





instead type


Create Trigger Trigger name


on table name


Instead of Operations (Update,delete)


as


SQL statement





Instance 1:


a change in the order Quantity (column name num) in the Order (table orders), the trigger is first in the Customer (table customer)


obtains the user's credit rating (the column name is level) and then takes the rank out of the credit limit (Creit)


the upper and lower limits of the ordered quantity permitted, and the final comparison of the order quantity in the orders is in compliance with the limit.





Code:


Create Trigger Num_check


On orders


after Insert,update


as


if update (num)


begin


if exists (select a.* from orders a join the customer B on A.customerid=b.customerid


join Creit C on B.level=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:


In a payroll management system, when a company adjusts the monthly salary of an employee, it usually modifies the salary column in the table staff and then


table Employee Records modify salary adjustment time and salary


           


Create Trigger Compensation


on employee


after update


as


if @ @rowcount =0 return


if Update (payroll)


begin


Insert Employee record


Select employee number, salary, GETDATE ()


from inserted


End