SQL Server database DML trigger "one"

Learn about DML triggers in SQL Server database today (DDL triggers have time to continue learning later).

The trigger executes the SQL statement when you delete a piece of content that creates a trigger in the table.

1. The Prime Minister first creates a table with a table named [Test] with three fields, namely [name], Sex, place.

1  UseInter2 Create TableTest3 (4     [Name] varchar( -) not NULL,5Sexvarchar( -) not NULL,6Placevarchar( -) not NULL7)

2. Then add content to the fields in the table:

1  UseInter2 Insert  intoTest ([Name], Sex, place)Values('Zhang San','male','Beijing')3 Insert  intoTest ([Name], Sex, place)Values('John Doe','female','Shanghai')4 Insert  intoTest ([Name], Sex, place)Values('Harry','male','Guangzhou')

After adding:

3. Create a trigger:

1 SetAnsi_nulls on2 SetQuoted_identifier on3 GO4 -- =============================================5 --Author:h_f_us6 --Create date:2014 December 28 20:29:477 --Description: Test trigger8 -- =============================================9 ALTER TRIGGER [dbo].[FIRST_TG]Ten     on  [dbo].[Test] OneAfterDELETE A  as  - BEGIN -  the Insert  intoTest ([Name], Sex, place)Values('Test','Test','Test') -  - END

4. After that, if you execute a DELETE statement, such as:

1 Delete from Test where [Name] = ' Zhang San '

5. The trigger is touched and executes the following SQL statement:

1 Insert  into Test ([Name]values ('test'test  "Test")--a pre-written SQL statement in the trigger

6. After the statement is executed,:

Commentary

1. In the trigger SQL statement, ALTER TRIGGER is followed by the name of the trigger and is arbitrarily selected in accordance with the SQL naming convention.

2. The trigger SQL statement is followed by the name of the table to create the trigger table.

3. after after, the trigger is activated after which action is performed. Delete, insert, update, three types of operations, at least in the trigger to really have one, or can be any combination of three, the order is unrestricted.

4. This example uses the delete operation type, the other operation type is the same as above.

5. In SQL triggers, there is not only an after trigger but also a INSTEAD of Trigger. The former is the activation trigger after the SQL statement is executed, which is the case written today. The latter is to activate the trigger before the SQL statement executes.

The use of INSTEAD of triggers is written in {SQL Server database triggers using "two"}. If there is anything wrong, please correct me in the comment area, thank you first.

SQL Server database DML trigger "one"