SQL Server---Triggers

It's easy to use triggers in SQL Server for the first time today, and this article will simply introduce you to the triggers in SQL Server and their simple use. I will tell from the definition, the principle, the specific usage.

definition

A trigger (trigger) is a special stored procedure whose execution is not invoked by the program or manually, but is triggered by an event, such as when an operation on a table (insert,delete,update) activates it for execution.

principle

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

SQL Server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

DML triggers

When the data in a table in a database changes, including insert,update,delete arbitrary action, the trigger executes automatically if we write a corresponding DML trigger on the table.

DDL triggers

It is a new trigger for SqlServer2005, which is mainly used to audit and standardize operations on the structure of tables, triggers, and views in the database.

Logon triggers

The logon trigger fires the stored procedure in response to a logon event.

Specific Usage

Then knock the computer room charge system, when it comes to the students up and down, we have to T_line and t_online these two tables to operate, the next time we need to delete the T_online table information to write it to T_line. With the three-layer architecture refactoring, it is troublesome to get the data from one table back to the U layer and assign it to another entity as a parameter and eventually pass it to another table. So I thought of the trigger.

SET ansi_nulls ongoset quoted_identifier ongo--=============================================--author:< Lao Niu >-- Create Date: <2014-6-2 15:28:00>--description:< When deleting a student on the computer, add the relevant message                to the Student record table >--================ =============================create TRIGGER [dbo]. [Triq_t_onlinedelete]   On  [dbo].[ T_online]    instead of Insertas begindeclare @CardNo numeric (one, 0) declare @studentNo  numeric (one, 0) DECLARE @ Studentname varchar (10)--Get parameter information select @CardNo =cardno, @studentNo =studentno, @studentName =studentname from deleted-- Add student on-machine record insert into T_line (Cardno,studentno, Studentname) VALUES (@CardNo, @studentNo, @studentName)-- Delete Student information on machine delete t_online where Cardno [email Protected]endgo

(PS: The previous code (tested correctly) only uses a few fields in the table other fields can also be used in a similar way, especially to note how to get the down time (HH-MM-SS) and the next machine date (YYYY-MM-DD), for example: Select CONVERT (varchar (100) , GETDATE (), 24;select CONVERT (varchar), GETDATE (), ())

Experience

Write here and think of that sentence "not will be the motive force of discovery", we learn the process also used to SQL Server for a period of time, before we also exposed to a lot of theoretical things always feel very advanced appearance, always think I will simply add and remove changes can solve the current problem. Only when we no longer have to learn the new technology, we will feel, it is the same thing.