SQL Server trigger Learning (automatic numbering)

The common basic points are as follows:
1. There are two types of triggers: Data Definition Language trigger (DDL trigger) and data control language trigger (DML trigger ).
DDL trigger: the user responds when the user modifies the database structure by executing data definitions (CREATE, ALTER, DROP, or similar statements) on the database.
DML trigger: when a user performs data operations on the database, the code in the trigger is automatically called.
2. DML trigger categories: Insert trigger, Delete trigger, Update trigger, and any type of mixing above.
3. Trigger creation Syntax: Copy codeThe Code is as follows: create trigger <trigger name>
ON <table or view>
{FOR | AFTER} <[INSERT] [,] [UPDATE], [DELETE] >}| instean}
AS
<SQL statement>

4. The trigger must be attached to a table or view. The trigger cannot exist independently. AFTER or FOR triggers do not support views. instead of supports tables or views.
5. In the INSERT trigger, SQL Server creates a copy of the inserted row and inserts the copy into a special table Insert Table. the table only exists in the trigger scope.
6. In the DELETE trigger, SQL Server creates a copy of the row to be deleted and inserts the copy into a special table Delete table. The table only exists in the trigger scope.
7. In the UPDATE trigger, SQL Server considers that the updated record deletes the existing record and inserts the updated record. Therefore, the UPDATE trigger contains two special tables: Insert and Delete, it also exists only in the trigger scope, and the two tables have the same number of rows.
8. Trigger should be as short as possible, because the trigger and the statements in the trigger are processed together, that is, the trigger is complete until the statement execution is complete. If the code is long, the trigger runs for a long time.
The following is an example of automatic numbering:Copy codeThe Code is as follows: -- there are two tables, the customer table and the project table. The project number is automatically generated when a new project is created. The project number of each different customer starts from 1.
-- The project number is in the format of PJ + "-" + "customer number" + "-" + "date" + "-" + "Serial Number"
-- Such as Project No.: PJ-ABCD-120805-0001
Create table testAccount -- create a test customer table
(
TAccName nvarchar (100), -- CUSTOMER NAME
TAccId nvarchar (32) -- customer ID
)
Create table testProject -- create a test project table
(
TProName nvarchar (100), -- project name
TProId nvarchar (32), -- Project No.
TIdAcc nvarchar (100), -- customer ID
TProGuid nvarchar (64) -- guid
)
Go
Create trigger T_AutoNumber
On testProject
After insert
As
Begin
Declare @ one nvarchar (8), -- Part 1, PJ
@ Two nvarchar (32), -- Part 2, customer ID
@ Three nvarchar (8), -- Part 3, date
@ Four int, -- Part 4 of serial number, serial number
@ Guid nvarchar (64) -- guid
Set @ one = 'pj'
Set @ three = convert (varchar (8), GETDATE (), 112)
-- Obtains the Customer Code and guid of the Inserted data from the Inserted copy table.
Select @ two = tIdAcc, @ guid = tProGuid from Inserted
-- Get the last four digits of the number
Select @ four = max (cast (right (tProId, 4) as int ))
From testProject
Where tIdAcc = @ two
-- The serial number of each new customer starts from 1. An existing customer adds 1 to the maximum serial number.
If @ four is null
Set @ four = 0
Else
Set @ four = cast (@ four as int)
Set @ four = @ four + 1
Update testProject set tProId = @ one + '-' + @ two + '-' + @ three + '-' + right ('000000' + cast (@ four as varchar ), 4) where tProGuid = @ guid
End
Go
-- Generate test table data
Insert into testAccount values ('duck Company Limited ', 'xxygs ')
Insert into testAccount values ('Ugly duckling Ltd. ', 'cxy ')
Insert into testProject (tProName, tIdAcc, tProGuid) values ('duckling growth Project', 'xxygs ', newid ())
Insert into testProject (tProName, tIdAcc, tProGuid) values ('duck learning swimming Project', 'xxygs ', newid ())
Insert into testProject (tProName, tIdAcc, tProGuid) values ('Ugly duckling growth Project', 'cxy', newid ())
Select * from testProject
Drop table testAccount
Drop table testProject

9. debug the trigger: Create a query window, enter the code, and press F11 to run the following script one by one to enter the trigger. You can also set a breakpoint in the trigger and then run the F11 Statement by statement.Copy codeThe Code is as follows: begin tran
Insert into testProject (tProName, tIdAcc, tProGuid) values ('duckling growth Project', 'xxygs ', newid ())
Insert into testProject (tProName, tIdAcc, tProGuid) values ('duck learning swimming Project', 'xxygs ', newid ())
Insert into testProject (tProName, tIdAcc, tProGuid) values ('Ugly duckling growth Project', 'cxy', newid ())
If @ TRANCOUNT> 0
Rollback tran