Shulou(Shulou.com)05/31 Report--

How to implement triggers in sqlserver? aiming at this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

The common basic points are summarized as follows:

1. There are two types of triggers: data definition language triggers (DDL triggers) and data manipulation language triggers (DML triggers). DDL trigger: activate and respond when the user executes a data definition (CREATE, ALTER, DROP, or similar statement) to the database to modify the database structure. DML trigger: occurs when the user performs a data operation on the database, and the code in the trigger is called automatically.

2. DML trigger classification: Insert trigger, Delete trigger, Update trigger, any of the above types are mixed.

3. Trigger creation syntax: copy the code as follows: CREATE TRIGGER ON {{FOR | AFTER}} | INSTEAN OF} AS

4. Triggers must be attached to tables or views, and triggers cannot exist alone. AFTER or FOR triggers do not support views, and INSTEAD OF supports tables or views.

5. In an INSERT trigger, SQL Server creates a copy of the inserted row and inserts the copy into a special table Insert table that exists only in 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 in the trigger scope.

7. In the UPDATE trigger, SQL Server believes that the updated record is the deletion of the existing record and the insertion of the updated new record, so the UPDATE trigger contains two special tables, Insert and Delete, and only exists in the trigger scope, and the number of rows of the two tables is exactly the same.

8. The trigger is as short as possible, because the trigger and the statement within the trigger are processed together, that is, the trigger is not complete until the statement execution is completed. If the code is long, the trigger will take a long time to run. The following is an example of implementing the automatic numbering function: the copy code is as follows:-- there are two tables, the customer table and the project table, and the project number is automatically generated when you create a new project. The project number for each different customer starts at 1-the project number format is PJ+ "-" + "customer number" + "+" date "+" + "serial number"-such as project number: PJ-ABCD-120805-0001 create table testAccount-create a test customer table (tAccName nvarchar -- customer name tAccId nvarchar (32)-- customer number) create table testProject-- create test item list (tProName nvarchar),-- Project name tProId nvarchar (32),-- Project number tIdAcc nvarchar (100),-- customer number tProGuid nvarchar (64)-- guid) go create trigger T_AutoNumber on testProject after insert as begin declare @ one nvarchar (8),-- number one PJ @ two nvarchar (32),-- part II, customer ID @ three nvarchar (8),-- part III, date @ four int,-- part IV. Serial number @ guid nvarchar (64)-- guid set @ one='PJ' set @ three= convert (varchar (8), GETDATE (), 112)-- get the customer code and guid select @ two=tIdAcc,@guid=tProGuid from Inserted from the Inserted replica table for the currently inserted data-- get the last four digits of select @ four=max (right (tProId,4) as int) from testProject where tIdAcc=@two-- the serial number for each new customer starts at 1. Existing customers add 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 (' duckling Limited', '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 (' duckling 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 trigger: create a new query window, enter the code, press F11 to run the following script statement by statement, enter the trigger. You can also set a breakpoint in the trigger and press F11 to execute sentence by sentence. The copy 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 (' duckling learning swimming project', 'XXYGS',newid ()) insert into testProject (tProName,tIdAcc,tProGuid) values (' ugly duckling growth project', 'CXY',newid ()) if @ @ TRANCOUNT > 0 rollback tran

This is the answer to the question about how to implement the trigger in sqlserver. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.