SQL Server---triggered

Today's first SQL Server trigger feeling is handy, this article will introduce you to a simple SQL Server trigger and simple use.

I will make sure that it, the principle, the use details are all about.

definition

A trigger (trigger) is a special stored procedure that is not called by a program. It is not started manually, but is triggered by an event, for example, when a table is manipulated (insert. Delete Update) is activated when it is run.

principle

Triggers can query other tables. And can include complex SQL statements.

Triggers can also be used to enforce referential integrity. To preserve the relationships defined between these tables in order to add, update, or delete rows in multiple tables.

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

DML triggers

When the data in a table in the database changes. Contains insert,update,delete discretionary operation, assuming that we write the corresponding DML trigger on the table, then the trigger will run itself.

DDL triggers

It is a new trigger for SqlServer2005. Used primarily for auditing and specification of tables in a database, triggers. Operations on structures such as views.

Logon triggers

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

Detailed usage Methods

Then knock the computer room charge system time. When it comes to students getting on and off, we have to operate on the two tables of T_line and t_online, and we need to delete the information from the T_online table to write it to t_line when we get off the machine. When using a three-tier architecture refactoring. Immediately think of getting data from a table to return to the U-layer and then assign it to another entity as a parameter finally passed in and a table is very troublesome.

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:< Delete the student on the same time the relevant letter--------------                to the students on the Machine 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 reference information Select @CardNo =cardno, @studentNo =studentno, @studentName =studentname from deleted-- Join the 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 used a few fields in the table other fields can also be used similar methods, especially to note how to get the machine time (HH-MM-SS) and the machine date (YYYY-MM-DD), such as: Select CONVERT (varchar (100 ), GETDATE (), 24. Select CONVERT (varchar (+), GETDATE (),))

Experience

Written here I thought of the phrase "the driving force was found, but" we also used the learning process SQL Server has for some time, in our contact with the theory of so many things contact has always felt very deep way. I will always think that a simple crud can solve the immediate problem.

We will no longer be only when we learn a new technology, will feel. There was such a thing.

SQL Server---triggered