SQL Server --- trigger

It is very convenient to use the trigger in SQL Server for the first time today. This article will briefly introduce the trigger in SQL Server and its simple use. I will describe the definition, principle, and usage of this document.

Definition

A trigger is a special stored procedure. Its execution is not called by a program or started manually, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution.

Principle

Triggers can query other tables and contain complex SQL statements. Triggers can also be used to force reference integrity so that the relationships defined between these tables are retained when rows are added, updated, or deleted in multiple tables.

SQLServer includes three common types of triggers: DML triggers, DDL triggers, and logon triggers.

DML triggers

When the data in the table in the database changes, including any insert, update, delete operations, if we write the corresponding DML trigger to the table, the trigger is automatically executed.

DDL trigger

It is a new trigger added by SqlServer2005. It is mainly used for review and standardization of database table, trigger, view and other structural operations.

Logon trigger

The login trigger will trigger the Stored Procedure in response to the LOGON event.

Usage

When you repeat the data center charging system, we need to operate the T_Line and T_Online tables when it comes to the case that students are on or off the server, when the machine is down, we need to delete the information in the T_Online table and write it into T_Line. When the layer-3 architecture is used for restructuring, it is very troublesome to get data from a table and return the U layer to assign the value to another object as a parameter and finally pass it into another table. So I thought of the trigger.

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO -- ============================ ============ -- Author: <old bull> -- Create date: <15:28:00> -- Description: <When deleting a student on the computer, add the relevant information to the student on-computer record table at the same time> -- ================ ==================================== create trigger [dbo]. [triq_T_OnlineDelete] ON [dbo]. [T_Online] instead of InsertAS BEGINdeclare @ CardNo numeric (11, 0) declare @ studentNo numeric (11, 0) declare @ studentName varchar (10) -- Obtain the parameter Information select @ CardNo = cardNo, @ studentNo = studentNo, @ studentName = studentName from deleted -- add an insert into T_Line (cardNo, studentNo, studentName) values (@ CardNo, @ studentNo, @ studentName) -- delete the student information on the computer: delete T_Online where cardNo = @ CardNoENDGO

(PS: the code in the previous section (tested and correct) can only use several fields in the Table. Other fields can also be used in a similar way. Pay special attention to how to obtain the downtime (hh-mm-ss) and the offline date (yyyy-mm-dd), for example: Select CONVERT (varchar (100), GETDATE (), 24; Select CONVERT (varchar (10), getdate (), 120 ))

Experience

Here I think of the phrase "not the motive force of discovery". We have also used SQL Server for some time in our learning process, we have also come into contact with a lot of theoretical things, and we always think that I will simply add, delete, modify, and query to solve the current problem. We can only feel that this is the case when we learn new technologies.