触发器的一些用法举例

--insert 触发器
create trigger tri_insert
on student
for insert
as
declare @student_id char(10)
select @student_id=s.student_id from
student s inner join inserted i
on s.student_id=i.student_id
@student_id='0000000001'
begin
raiserror('不能插入1学号!',16,8)
rollback tran
end
go ]
= --update触发器
create trigger tri_update
on student
for update
as
update(student_id)
begin
raiserror('学号不能修改!',16,8)
rollback tran
end
go
--delete触发器
create trigger tri_delete
on student
for delete
as
declare @student_id varchar(10)
select @student_id=student_id from deleted
@student_id='admin'
begin
raiserror('',16,8)
rollback tran
end
禁止删除触发器
CREATE TRIGGER NOdelete
on myabc
after delete
as
if (select count(*) from deleted)>1
begin
raiserror
('你不能删除客户',16,1)
rollback transaction
end
---打印出一条插入的数据
select top 10 * from p_customer
CREATE TRIGGER PrintValue
on myabcd
after insert
as
declare @var nvarchar(50)
select @var=cstname from inserted
print @var
insert myabcd (cstguid,cstname)
select '985575ED-CC05-4B93-97A6-0007D741D170','trigger'
--打印多条
CREATE TRIGGER PrintValue1
on myabcd
after insert
as
select cstname from inserted
insert myabcd (cstguid,cstname)
select '985575ED-CC05-4B93-97A6-0007D741D170','trigger' ---撰写触发器,使用join来处理库存的更新
--创建表略
---当订单多一条数据时库存表就相应减少,这由触发器来完成
CREATE TRIGGER orderdetails on [order datails]
after insert
as
update products set unitinstock=unitinstock-i.quantity
from [order details] as od
inner join inserted as i
on od.productid=i.productid
insert [order details] values (1,1,20,1,0)
--值被删除时触发器列出被删除的值
CREATE TRIGGER PrintValue2
on myabcd
after delete
as
select cstname from deleted
--定义Title标题表,Article 文章表
--当删除文章区文章时删除相应标题
CREATE TRIGGER dArticle on Article
AFTER DELETE
AS
DELETE FROM Title from title as t
left join article as a on a.titleid=t.titleid
where a.titleid is null
---与添加,删除不同的是SQL server不存在updated表,而是用inserted来存放修改后的数据,用deleted存放修改前的数据
CREATE TRIGGER UMyabc
ON Myabcd
after update
as
declare @var1 varchar(50)
declare @var2 varchar(50)
select @var1=cstname from deleted
select @var2=cstname from inserted
print '修改前数据[email='+@var1]'+@var1[/email]
print '修改后数据[email='+@var2]'+@var2[/email]
update top (1) myabcd set cstname='测试触发器'
--update函数判断究竟该字段是否被修改
CREATE TRIGGER UPmyabc
on myabcd
after update
as
if update(cstname)
print '修改'
else
print '未改'
update myabcd set cstname=2 where 1=2
select * from myabcd
---关闭停用触发器
disable trigger UPmyabc on myabcd
disable trigger Umyabc on myabcd
--启用触发器
enable trigger UPmyabc on myabcd
---instead of 触发器 (可对表和视图有效,对有instead of触发器的对象执行delete update insert 命令时并没有改动数据
---需要在instead of 触发器中写替代命令
---主要用于分区表,分区视图,当视力由UNION ALL连接而成时无法直接更新视图,就需要instead of触发器来完成
create trigger ofmyabc on myabcd
instead of delete,insert,update
as
print '没有更新数据'
--测试
delete myabcd
select * from myabcd
update myabcd set cstname=8
insert myabcd
select top 1 * from p_customer
disable trigger ofmyabc on myabcd
--建立数据
drop table wang
drop table liu
select * into wang from p_customer where cstname='龙飞'
select * into liu from p_customer where cstname='李寻欢' create view xs
as
select * from wang
union all
select * from liu
--建立instead of 触发器
alter trigger inx on xs
instead of update
as
declare @name varchar(100)
set @name=(select cstname from inserted)
if @name='龙飞'
print '更新龙飞'
else if @name='李寻欢'
print '更新李寻欢'
--测试
update xs set gender='女' where cstname='龙飞'
update xs set gender='女男' where cstname='李寻欢'
---DDL触发器响应表级别或服务器级别的事件
CREATE TRIGGER safety
on database
for drop_table
as
raiserror
('你不能删除表',16,1)
rollback transaction
go
--测试
drop table myabc
select * from myabc
disable trigger safety on database
--对特定表保护
create TRIGGER safety1
on database
for drop_table
as
declare @data xml
declare @tablename nvarchar(max)
set @data=eventdata()
select
@tablename=eventdata().value('(/event_instance/objectname)[1]','nvarc har(max)')
if @tablename='myabc'
raiserror('你不能删除这个表',16,1)
PRINT '你不能删除这个表'
rollback tran
--测试
drop table myabc
---建立服务器级别的DDL触发器
CREATE TRIGGER ddl_trig_login
on all server
for ddl_login_events
as
print '登录事件'
select
eventdata().value('(/event_instance/tsqlcommand/commandtext)[1]','nva rchar(max)')
sp_addlogin 'wangyong','1234123456789'
drop login wangyong。