Shulou(Shulou.com)06/01 Report--

This article mainly explains "how to create SQL SERVER triggers", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let Xiaobian take you to learn "How to create SQL SERVER trigger"!

Trigger is a special kind of stored procedure. Trigger is triggered by event and automatically invoked, while stored procedure must be invoked by stored procedure name.

1. Definition of trigger

Triggers are special stored procedures that are automatically executed when an insert, update, or delete operation is performed on a table. Triggers are often used to enforce business rules, and triggers are high-level constraints that can define more complex constraints than CHECK constraints: they can execute complex SQL statements (if/while/case), and they can reference columns in other tables. Trigger is defined on a specific table, related to the table, automatically triggers execution, cannot be called directly, is a transaction (can be rolled back).

II. Trigger classification

Triggers in SQL SERVER can be divided into two categories: DML triggers and DDL triggers. DML triggers are aimed at tables, and DDL triggers affect various data definition language statements. These statements include create, alter and drop statements.

DML triggers are divided into:

1, after trigger (after trigger)

a, insert trigger

b. Update trigger

c. delete trigger

2. instead of trigger

After triggers require that an action (insert, update, delete) be performed before the trigger can be triggered, and can only be defined on a table. Instead of a trigger means that it does not execute its defined action (insert, update, delete) but only executes the trigger itself, which takes precedence over the execution of the trigger statement.

Triggers have two special tables: the inserted table and the deleted table

These two tables are logical tables and virtual tables. When the trigger is triggered, the system automatically creates these two tables in memory and will not store them in the database. Both tables are read-only and cannot be modified. The result of these two tables is always the same structure as the table applied by the trigger. When the trigger completes its work, both tables are deleted. The inserted table temporarily stores the inserted or updated record rows. You can check whether the inserted data meets the business requirements from the inserted table. If not, an error message will be reported to the user and the insertion operation will be rolled back. The deleted table temporarily stores the record rows before deletion or update. You can check whether the deleted data meets the business requirements from the deleted table. If not, an error message will be reported to the user and the deletion operation will be rolled back. When updating data, delete the table record first, and then insert a record. In both inserted and deleted tables, there are updated data records.

The inserted and deleted tables hold information:

modification operation

inserted table

deleted tables

INSERT record

Store new records

no

Delete records

no

Store deleted records

UPDATE record

Storage of updated records

Store pre-update records

III. Creating triggers

Grammar:

CREATE TRIGGER trigger_name ON table_name [WITH ENCRYPTION] FOR [DELETE, INSERT, UPDATE] AS T-SQL statements GO

WITH ENCRYSTATION SQL text representing the encryption trigger definition

Delete, INSERT, UPDATE Specify the type of trigger

1. Create a trigger of insert type

insert trigger

--Insert a piece of data in GradeInfo table, insert a record in MyStudentInfo table IF (object_id ('tr_insert','tr') is not null) drop trigger tr_insertGOCREATE trigger tr_inserton GradeInfoafter insert --insert trigger as begin --Define variables declare @GradeId int --Query inserted record information in inserted table select @GradeId=id from INSERTED --Insert data into MyStudentInfo table insert INTO MyStudentInfo (GradeId) VALUES (@GradeId) print 'inserted successfully! ' end

insert data

insert INTO GradeInfo VALUES (11,'C++')

query data

select * from MyStudentInfo where GradeId=11

2. Delete trigger

Delete data from MyStudentInfo table and insert backup table

--Delete data from MyStudentInfo table, insert backup table IF (object_id ('tr_Delete','tr') is not null) drop TRIGGER tr_Delete GO CREATE trigger tr_Delete on MyStudentInfo for delete as begin print 'backing up data... ' IF (object_id ('MyStudentInfo_Back',' U') is not null) --table exists, insert data directly insert INTO MyStudentInfo_Back SELECT * from DELETED else select * into MyStudentInfo_Back from DELETED PRINT 'backup complete' end

Query MyStudentInfo table data before deleting

select * from MyStudentInfo

Delete data with id=9

delete FROM MyStudentInfo where Id=9

Query backup table data

select * from MyStudentInfo_Back

3. update trigger IF (object_id ('tr_Update','tr') is not null) drop TRIGGER tr_Update GO CREATE trigger tr_Update on MyStudentInfo for update as begin --Declarative variables, storing pre-and post-update names declare @OldName varchar(16),@NewName varchar(16) select @OldName=name from DELETED print 'name before update:'+@OldName select @NewName=name from INSERTED print 'Updated Name:'+@NewName end

Update Zhang San to "Zhang San Test"

Myupdate StudentInfo SET Name ='Zhang San Test', where Id=1

update update column-level triggers

--update Update column-level triggers IF (object_id('tr_update_column','tr') is not null) drop TRIGGER tr_update_column GO CREATE trigger tr_update_column on GradeInfo for update as begin IF(update(id)) begin print 'System prompt: Primary key ID cannot be updated' rollback end end

Update id column

update GradeInfo SET Id=15 where Id=4

4, instead of trigger

Instead of a trigger means that it does not perform its defined action (insert, update, delete) but only the contents of the trigger itself, which takes precedence over the execution of the defined SQL statement

Grammar:

create trigger tgr_nameon table_namewith encryption instead of update... as begin T-SQL end

Creating instead of triggers

--create instead of trigger/*MyStudentInfo table before inserting data, first determine whether there is a corresponding class ID in GradeInfo table, if not, insertion is not allowed, if there is, insert */IF (object_id ('tr_instead Of ','tr') is not null) drop TRIGGER tr_insteadOfGOCREATE trigger tr_insteadOfon MyStudentInfoinstead of insertas begin IF exists(SELECT * FROM GradeInfo WHERE Id=(SELECT GradeId FROM INSERTED)) print 'class exists, insert' else begin print 'This class does not exist, cannot be inserted' rollback end end

Test 1, insert non-existent class id

insert INTO MyStudentInfo (GradeId) VALUES (15)

Test 2, insert existing class id

insert INTO MyStudentInfo (GradeId) VALUES (5)

DDL trigger create trigger tr_DDL on database for DROP_TABLE,ALTER_TABLE as begin t think about deleting the library! Type your code properly.' rollback --rollback end

test deletion table

drop TABLE MyStudentInfo

Test modification table structure

alter table MyStudentInfoalter column Name varchar(32)

Disable DML Triggers

disable trigger tr_insteadOf on MyStudentInfo

Enable DML Triggers

enable trigger tr_insteadOf on MyStudentInfo

Disable DDL triggers

disable trigger tr_DDL on database

Enable DDL triggers

enable trigger tr_DDL on database At this point, I believe that everyone has a deeper understanding of "how to create SQL SERVER triggers," so let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.