Trigger design tips and Examples

Keyword: trigger instance

In database design, there are two ways to set automatic data processing rules, one is conditional constraints and the other is triggers. In general, condition constraints are easier to set and maintain than triggers, and the execution efficiency is better. However, condition constraints can only be used for simple column check of data. complex operations such as multi-Table operations are involved.
Trigger is required.

There are two virtual tables in a database system that are used to store the changes recorded in the Table: virtual table inserted and virtual table deleted.

New records are stored when table records are added. New records are not stored.
New record used for update stored during Modification
The deleted records are not stored during deletion.

Trigger type and trigger time

After trigger: when the data changes, it will perform necessary aftercare and handling for the changed data. If an error is found, it will be rolled back by the transaction (rollback transaction) reply all the updated information for this operation.

Istead trigger: the trigger time occurs before the data change, and how the data changes depends on the trigger.

Now we will introduce how to create a trigger:

After type:
Create trigger name
On Table Name
After Operation (insert, update)
As
SQL statement

Instead of type:
Create trigger name
On Table Name
Instead of operation (Update, delete)
As
SQL statement

Instance 1:

When the order quantity (column name: num) in an order (Table orders) changes, the trigger first obtains the user's credit grade (column name: level) from the customer (Table customer ), then, go to the credit line (creit) to obtain the upper and lower limit of the order quantity for this level of permission, and finally compare whether the order quantity in the order meets 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 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 'admin', 'customer's order quantity does not meet the limited'
End
End

Instance 2:

In the wage management system, when the company adjusts the monthly salary of an employee a, it usually first modifies the salary column in the table employee, then, modify the salary adjustment time and salary in the employee records in the table.

Create trigger compensation
on employee
after update
as
If @ rowcount = 0 return
If update (salary)
begin
insert employee record
select employee number, salary, getdate ()
from inserted
end