创建语法
create trigger tgr_name on table_name with encryption instead of update... as T-SQL
创建instead of触发器
if (object_id('tgr_classes_inteadOf', 'TR') is not null) drop trigger tgr_classes_inteadOf go create trigger tgr_classes_inteadOf on classes instead of delete/*, update, insert*/ as declare @id int, @name varchar(20); --查询被删除的信息,病赋值 select @id = id, @name = name from deleted; print 'id: ' + convert(varchar, @id) + ', name: ' + @name; --先删除student的信息 delete student where cid = @id; --再删除classes的信息 delete classes where id = @id; print '删除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功!'; go --test select * from student order by id; select * from classes; delete classes where id = 7;
显示自定义消息raiserror
if (object_id('tgr_message', 'TR') is not null) drop trigger tgr_message go create trigger tgr_message on student after insert, update as raisError('tgr_message触发器被触发', 16, 10); go --test insert into student values('lily', 22, 1, 7); update student set sex = 0 where name = 'lucy'; select * from student order by id;
修改触发器
alter trigger tgr_message on student after delete as raisError('tgr_message触发器被触发', 16, 10); go --test delete from student where name = 'lucy';
启用、禁用触发器
--禁用触发器 disable trigger tgr_message on student; --启用触发器 enable trigger tgr_message on student;
查询创建的触发器信息
--查询已存在的触发器 select * from sys.triggers; select * from sys.objects where type = 'TR'; --查看触发器触发事件 select te.* from sys.trigger_events te join sys.triggers t on t.object_id = te.object_id where t.parent_class = 0 and t.name = 'tgr_valid_data'; --查看创建触发器语句 exec sp_helptext 'tgr_message';
示例,验证插入数据
if ((object_id('tgr_valid_data', 'TR') is not null)) drop trigger tgr_valid_data go create trigger tgr_valid_data on student after insert as declare @age int, @name varchar(20); select @name = s.name, @age = s.age from inserted s; if (@age < 18) begin raisError('插入新数据的age有问题', 16, 1); rollback tran; end go --test insert into student values('forest', 2, 0, 7); insert into student values('forest', 22, 0, 7); select * from student order by id;
示例,操作日志
if (object_id('log', 'U') is not null) drop table log go create table log( id int identity(1, 1) primary key, action varchar(20), createDate datetime default getDate() ) go if (exists (select * from sys.objects where name = 'tgr_student_log')) drop trigger tgr_student_log go create trigger tgr_student_log on student after insert, update, delete as 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'); end go --test insert 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;
总结
本篇文章就到这里了,希望能够给你带来帮助,也希望您能够多多关注的更多内容!
更多SQL内容来自木庄网络博客