Data Center reconstruction (4) -- trigger usage

The previous article "Data Center reconstruction (3) -- stored procedures" describes the use of stored procedures. Next, we will introduce the use of triggers. When it comes to triggers, we are no stranger. we are involved in a lot of relevant knowledge, but lack of practical applications. Through this IDC fee, I have a further understanding of the trigger.

1. Introduction

A trigger is also a special feature related to table events.Stored Procedure. It is triggered by an event. When you perform operations (insert, delete, update) on a table, it is activated for execution. It is often used to strengthen data integrity constraints and business rules. The difference between it and the stored procedure is that the trigger cannot execute the excute statement call, but the transact_ SQL statement is automatically executed when the user executes it.

2. Classification

SQL Server includes three types of triggers:

1) DML triggers (commonly used)

If we acknowledge the corresponding DML trigger to a table and the data in the database table changes accordingly (insert, delete, update), the trigger is automatically executed. Its main function is to enforce business rules and extend SQL server constraints and default values.

It can be divided:

A. After trigger

Including the insert trigger, update trigger, and delete trigger. The trigger can only be triggered after these operations are executed and can only be defined on tables.

B. Instead of triggers

Instead of performing the defined operations, you can only execute the trigger itself. An instead of trigger can be defined on a table or in an attempt.

2) DDL triggers

It is mainly used to review and standardize database operations on tables, triggers, views, and other structures. When the database structure changes (modifications and new columns, new tables, etc.), it is mainly used to record the database modification process and restrict the programmer's modifications to the database.

3) logon trigger

Stored Procedures that respond to login time. After the login trigger completes the authentication phase, it is triggered before the user session is established. If the authentication fails, the logon trigger is not triggered.

3. Functions

A. Allow/restrict table modifications
B. automatically derived columns, such as auto-increment Fields
C. Forced Data Consistency
D. provide audit and logging
E. Prevent invalid transaction processing

4. instance applications

To familiarize yourself with the trigger in practice, the trigger is applied to the data center billing registration function. This shows the trigger creation process.

This trigger is triggered by inserting data in the insert student information table to insert records in the Registry and recharge table, as follows:

1) create a trigger in the t_stuinfo table

2) write statements

Use [jf_sys] Go/****** object: trigger [DBO]. [register] script Date: 21:06:12 ******/set ansi_nulls ongoset quoted_identifier ongoalter trigger [DBO]. [register] -- create a trigger on [DBO]. [t_stuinfo] After insertas declare @ cardid varchar (10), -- parameter @ stuid varchar (10), @ regdate varchar (10), @ regtime varchar (10 ), @ userid varchar (10), @ regcash varchar (15), @ checkstatus varchar (10) Select @ cardid = cardid, @ userid = userid from insertedselect @ regcash = stucash from insertedselect @ stuid = stuid from insertedset @ regdate = convert (varchar (10), getdate (), 120) -- Get the date set @ regtime = convert (varchar (10), getdate (), 108) -- get the time begin -- the operation is triggered: insert into t_reginfo (cardid, stuid, regdate, regtime, userid, regcash, checkstatus) values (@ cardid, @ stuid, @ regdate, @ regtime, @ userid, @ regcash, 'uncheck') insert into t_rechargeinfo (cardid, recdate, rectime, reccash, userid, checkstatus) values (@ cardid, @ regdate, @ regtime, @ regcash, @ userid, 'uncheck') End

There may be deficiencies in the application of new knowledge. If there is anything inappropriate, please point it out!