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

Editor to share with you what is the use of SQL Server triggers, I hope you will gain something after reading this article, let's discuss it together!

1. What is a trigger

A special stored procedure that is automatically executed when a trigger inserts, updates, or deletes a table. Triggers are generally used for check constraints that are more complex. The difference between a trigger and an ordinary stored procedure is that a trigger is when it operates on a table. When operating such as update, insert, delete, the system will automatically call and execute the corresponding trigger on the table. Flip-flops in SQL Server 2005 can be divided into two types: DML flip-flops and DDL flip-flops, in which DDL flip-flops can be fired by affecting a variety of data definition language statements, including create, alter and drop statements.

2. DML triggers are divided into 2.1after triggers (triggered later)

A, insert trigger

B, update trigger

C, delete trigger

2.2 instead of trigger (previously triggered)

Among them, the after trigger requires that the trigger is triggered only after performing an operation insert, update, delete, and can only be defined on the table. The instead of trigger means that it does not perform its defined operations (insert, update, delete), but only executes the trigger itself. Instead of triggers can be defined either on the table or on the view.

Triggers have two special tables: insert table (instered table) and delete table (deleted table). These two are logical tables and virtual tables. There are two tables created by the system in memory and are not stored in the database. And both tables are read-only, so you can only read the data and not modify it. The results of these two tables are always the same as the structure of the table applied by the modified trigger. When the trigger completes its work, the two tables are deleted. The data of Inserted table is inserted or modified, while the data of deleted table is pre-updated or deleted.

Operation on the table

Inserted logic table

Deleted logic table

Add record (insert)

Store additional records

None

Delete record (delete)

None

Store deleted records

Modify record (update)

Store updated records

Store pre-updated records

Update data is to delete the table record, and then add a record. In this way, the data after update will be recorded in both the inserted and deleted tables. Note that the trigger itself is a transaction, so you can do some special checks on the modified data in the trigger. If it is not satisfied, you can use transaction rollback and undo the operation.

3. Create a trigger

Grammar

Create trigger tgr_nameon table_namewith encrypion-encryption trigger for update...as Transact-SQL3.1 create insert type trigger-create insert insert type trigger if (object_id ('tgr_classes_insert',' tr') is not null) drop trigger tgr_classes_insertgocreate trigger tgr_classes_inserton classes for insert-insert trigger as-define variables declare @ id int, @ name varchar (20), @ temp int -- query the inserted table for inserted record information select @ id = id, @ name = name from inserted; set @ name = @ name + convert (varchar, @ id); set @ temp = @ id / 2; insert into student values (@ name, 18 + @ id, @ temp, @ id); print 'added student successfully!' ; go-- inserts data insert into classes values ('Class 5', getDate ());-- query data select * from classes;select * from student order by id

Insert trigger, which adds a newly inserted record to the inserted table.

3.2Create delete type trigger-delete delete type trigger if (object_id ('tgr_classes_delete',' TR') is not null) drop trigger tgr_classes_deletegocreate trigger tgr_classes_deleteon classes for delete-delete trigger as print 'backup data …' ; if (object_id ('classesBackup',' U') is not null)-classesBackup exists, data insert into classesBackup select name is inserted directly, createDate from deleted; else-there is no classesBackup creation and then insert select * into classesBackup from deleted; print 'backup data successfully!' ; go---- does not show the number of affected rows-- set nocount on;delete classes where name = 'Class 5';-- query data select * from classes;select * from classesBackup

The delete trigger saves the data just deleted in the deleted table when it is deleted.

Create update type trigger-- update update type trigger if (object_id ('tgr_classes_update',' TR') is not null) drop trigger tgr_classes_updategocreate trigger tgr_classes_updateon classes for updateas declare @ oldName varchar (20), @ newName varchar (20);-- data select @ oldName = name from deleted before update If (exists (select * from student where name like'%'+ @ oldName +'%') begin-updated data select @ newName = name from inserted; update student set name = replace (name, @ oldName, @ newName) where name like'%'+ @ oldName +'%'; print 'cascade modified data successfully!' ; end else print 'there is no need to modify the student!' ; go-- query data select * from student order by id;select * from classes;update classes set name = 'Class 5' where name = 'Class 5'

After updating the data, the update trigger saves the data before the update in the deleted table, and the updated data in the inserted table.

3.4 update update column-level trigger if (object_id ('tgr_classes_update_column',' TR') is not null) drop trigger tgr_classes_update_columngocreate trigger tgr_classes_update_columnon classes for updateas-column-level trigger: whether the class creation time if (update (createDate)) begin raisError ('system hint: class creation time cannot be modified!' , 16, 11); rollback tran; endgo-- test select * from student order by id;select * from classes;update classes set createDate = getDate () where id = 3ten update classes set name = 'Class four' where id = 7

Update column-level triggers can use update to determine whether to update column records

4. Instead of type trigger

An instead of trigger means that it does not perform its defined operations (insert, update, delete), but only executes the contents of the trigger itself.

Create syntax:

Create trigger tgr_nameon table_namewith encryption instead of update...as T-SQL4.1 creates instead of trigger if (object_id ('tgr_classes_inteadOf',' TR') is not null) drop trigger tgr_classes_inteadOfgocreate trigger tgr_classes_inteadOfon classes instead of delete/*, update, insert*/as declare @ id int, @ name varchar (20);-- query deleted information, select @ id = id, @ name = name from deleted Print'id:'+ convert (varchar, @ id) +', name:'+ @ name;-- first delete student message delete student where cid = @ id;-- then delete classes message delete classes where id = @ id; print 'delete [id:' + convert (varchar, @ id) +', name:'+ @ name +'] succeeded!' ; go--testselect * from student order by id;select * from classes;delete classes where id = 74.2 display custom messages raiserrorif (object_id ('tgr_message',' TR') is not null) drop trigger tgr_messagegocreate trigger tgr_messageon student after insert, updateas raisError ('tgr_message trigger triggered', 16, 10); go--testinsert into student values ('lily', 22, 1, 7); update student set sex = 0 where name =' lucy';select * from student order by id 4.3 modify trigger alter trigger tgr_messageon studentafter deleteas raisError ('tgr_message trigger is triggered', 16,10); go--testdelete from student where name = 'lucy';4.4 enable and disable trigger-disable trigger disable trigger tgr_messageon student;-- enable trigger information created by trigger enable trigger tgr_messageon student;4.5 query-query existing trigger select * from sys.triggers;select * from sys.objects where type =' TR' -- View trigger event select te.* from sys.trigger_events te join sys.triggers ton t.object_id = te.object_idwhere t.parent_class = 0 and t.name = 'tgr_valid_data';-- View create trigger statement exec sp_helptext' tgr_message' 4.6 example, verify that the inserted data if ((object_id ('tgr_valid_data',' TR') is not null) drop trigger tgr_valid_datagocreate trigger tgr_valid_dataon studentafter insertas declare @ age int, @ name varchar (20); select @ name = s.name, @ age = s.age from inserted s; if (@ age < 18) begin raisError ('there is a problem with age inserting new data', 16,1) Rollback tran; endgo--testinsert into student values ('forest', 2,0,7); insert into student values (' forest', 22,0,7); select * from student order by id 4.7 exampl Operation log if (object_id ('log',' U') is not null) drop table loggocreate table log (id int identity (1,1) primary key, action varchar (20), createDate datetime default getDate () goif (exists (select * from sys.objects where name = 'tgr_student_log')) drop trigger tgr_student_loggocreate trigger tgr_student_logon studentafter insert, update Deleteas if ((exists (select 1 from inserted)) and (exists (select 1 from deleted) begin insert into log (action) values ('updated') End else if (exists (select 1 from inserted) and not exists (select 1 from deleted)) begin insert into log (action) values ('inserted'); end else if (not exists (select 1 from inserted) and exists (select 1 from deleted)) begin insert into log (action) values (' deleted'); endgo--testinsert into student values ('king', 22, 1, 7); update student set sex = 0 where name =' king';delete student where name = 'king' Select * from log;select * from student order by id; after reading this article, I believe you have a certain understanding of "what is the use of SQL Server triggers". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.