Summary of common basic points are as follows:
1, there are two types of triggers: data definition language triggers (DDL triggers) and data manipulation language triggers (DML triggers).
DDL triggers: A response that occurs when a user modifies a database structure by performing a data definition (CREATE, ALTER, drop, or similar statement) to the database.
DML triggers: Occurs when a user performs a data operation on the database, and the code in the trigger is invoked automatically.
2. DML triggers Category: Insert trigger, delete trigger, UPDATE trigger, any type of blending above.
3. Trigger creation Syntax:
Copy Code code as follows:
CREATE TRIGGER <trigger name>
On <table or view>
{{{for| After} <[insert] [,] [update],[delete]>}| Instean of}
As
<sql statement>
4. Triggers must be attached to tables or views, and triggers cannot exist alone. After or for triggers do not support views, INSTEAD of support 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 that exists only within the trigger scope.
6. In the delete trigger, SQL Server creates a copy of the deleted row and inserts the copy into a special table delete table that exists only within the trigger scope.
7. In the update trigger, SQL Server considers that the updated record deletes the existing record and inserts the updated new record, so the update trigger contains the INSERT and delete two special tables, and only the trigger scope, where the rows of the two tables are exactly the same.
8, the trigger as short as possible, because the trigger and the statement within the trigger is processed together, that is, until the execution of the statement to complete the trigger is complete. If the code is very long, the trigger will run for a long time.
The following is an example of an automatic numbering feature:
Copy Code code as follows:
--There are two tables, the Customer table and the project table, which require that the project number be automatically generated when a new project is created, and that the project number of each different customer starts at 1
--Item number format is pj+ "-" + "customer number" + "-" + "date" + "-" + "" Serial number "
--such as project number: pj-abcd-120805-0001
CREATE TABLE Testaccount--Creating a Test Customer table
(
Taccname nvarchar (100),--Customer name
Taccid nvarchar (32)--Customer number
)
CREATE TABLE TestProject--Creating a test project table
(
Tproname nvarchar (100),--project name
Tproid nvarchar (32),--Item No.
TIDACC nvarchar (100),--Customer number
Tproguid nvarchar (--guid)
)
Go
Create Trigger T_autonumber
On TestProject
After insert
As
Begin
declare @one nvarchar (8),--numbering the first part, PJ
@two nvarchar (32),--numbered Part II, customer number
@three nvarchar (8),--Numbered Part III, date
@four int,--Numbered Part IV, serial number
@guid nvarchar (--guid)
Set @one = ' PJ '
Set @three = CONVERT (varchar (8), GETDATE (), 112)
--Get the customer code and GUID for the currently inserted data from the inserted copy list
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
--For each new customer, the serial number starts at 1, and the customer has the largest serial number plus 1.
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 (' 0000 ' +cast (@four as varchar), 4 where tproguid=@ Guid
End
Go
--Generate Test table data
INSERT into testaccount values (' Small Duck Co., ltd ', ' Xxygs ')
INSERT into testaccount values (' Ugly Duckling Co., ltd ', ' Cxy ')
Insert into TestProject (TPRONAME,TIDACC,TPROGUID) VALUES (' Duckling Growth project ', ' Xxygs ', newid ())
Insert into TestProject (TPRONAME,TIDACC,TPROGUID) VALUES (' Ducklings learn 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 Trigger: Create a new Query window, enter the code, press F11 to run the following script, into the trigger. You can also set a breakpoint in a trigger and then press F11 to execute it on a per-statement basis.
Copy Code code as follows:
Begin TRAN
INSERT INTO TestProject (TPRONAME,TIDACC,TPROGUID) VALUES (' small Duck growth project ', ' Xxygs ', newid ())
INSERT into TestProject (TPRONAME,TIDACC,TPROGUID) VALUES (' Ducklings learn swimming project ', ' Xxygs ', newid ())
INSERT INTO TestProject (TPRONAME,TIDACC,TPROGUID) VALUES (' Ugly Duckling Growth project ', ' Cxy ', newid ())
if @ @TRANCOUNT >0
Ro Llback Tran
