使用触发器TRIGGER
INSERT TRIGGER
触发器是由SQL语句集组成的代码块,在响应某些动作时激活该语句集.一个触发器也可被解释为特定类型的存储过程,每当动作发生时执行该存储过程:每当基础表中数据受到数据操纵语言(DML)语句-INSERT,UPDATE,DELETE的影响时,触发器就被激发.
触发器的特征:
1. 当任何数据修改语句发出时,它被sql server自动调用
2. 在存储过程的情况下,它不能被显式地调用或执行
3. 它防止了对数据的不正确,未授权的,和不一致的改变
4. 它不能返回数据给用户
触发器语法:
CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR[INSERT | UPDATE | DELETE]
AS sql_statement
幻表:
当触发器激发对INSERT,DELETE,或UPDATE语句的响应时,两个特殊的表被创建:插入表(Inserted)和删除表(deleted),
示例:
create trigger trgInsertRequisiton
on Requistion
for insert
as
declare @VacnacyReported int
declare @ActualVacancy int
select @ActualVacancy = iBudgetedStrength - currentStrength
from Position join Inserted on
Position.cPositionCode = Inserted.cPositionCode
select @VancyReported = inserted.siNoOfVacancy
from inserted
if(@VancyReported > @ActualVacancy)
begin
print'The actual vacancies are less than the vacncies'
rollback transaction
end
return
DELETE TRIGGER
1.当试图从触发器表中删除一行时,DELETE TRIGGER被触发
2.使用DELETE TRIGGER触发器来实现引用完整性约束主要有三种途径:
1.串联方法:每当从主表中删除记录时,删除依赖表中的记录
2.限制方法:如果相尖记录出现在依赖表中,则限制从主表中删除记录
3.无效方法:每当一个记录从主表中删除时,把依赖表中所指列的值变为无效
select * from publisher;
create trigger trgfordel
on publisher
for delete
as
begin
print '您删除的数据的内容为'
select * from deleted
end
delete from publisher where pub_id = 9;
UPDATE TRIGGER
当一个UPADATE触发器被激发时,它使用关于其操作的两个逻辑表-包含原始行的deleted表和存储新行的insertd表.
例一:
create trigger trgUpdatePub
on Publisher
for update
as
if update(pub_id)
begin
print 'publisher id cannot be modified'
rollback tran
end
è当用户试图修改pub_id列时它就激发,它因此阻止用户修改pub_id的内容并回滚整个事务
例二:
create trigger trgUpdateContractRecruiter
on ContractRecruiter
for update
as
declare @AvgPercentageCharge int
select @AvgPercentageCharge = avg(siPercentageCharge)
from ContractRecruiter
if(@AvgPercentageCharge > 11)
begin
print 'The average cannot be more than 11'
rollback transaction
end
修改触发器语法:
alter trigger trigger_name
on table_name
[with encryption]
for[insert | delete | update]
as sql_statements
撤消触发器:
DROP TRIGGER trigger_name[,...n]
通过触发器加强数据的完整性
触发器可以用来确保和加强业务规则和数据完整性,如,只有库存中有该产品时,才可以出售,如果使用触发器,它将会对不正确的事务做检查,并确保只有有效的数据才能插入到表中.
例如:某种改变违反了引用完整性,那么所有这样的改变都被拒绝,因此所有试图改变数据库中的数据都被取消.
CREATE TRIGER trgUpdateDelete
ON TitleAuthor
FOR INSERT,UPDATE
AS
If (SELECT COUNT(*) FROM Titles t JOIN inserted i
ON t.Title_id = i.Title_Id) = 0
BEGIN
PRINT 'Invialid title ID entered'
ROLLBACK
END
IF(SELECT (COUNT(*) FROM Authors t JOIN inserted i
ON t.Au_Id = i.Au_Id) = 0
BEGIN
PRINT 'INVIALID author ID eneter'
ROLLBACK
END
多触发器:
SQL SERVER允许在给定表中定义多个触发器.这意味着单个DML语句可激活两个或多个触发器.触发器以创建次序被激活.
AFTER 和 INSTEAD OF 触发器
AFTER触发器在功能上,是在DML操作执行成功后.再执行的触发器.
如:
create triger trgDeletetitles
on Titles
AFTER DELETE
AS
PRINT 'DELETION SUCCESSFUL'
*********************************************************************
假如单个DML后有多个AFTER触发器,你可以通过使用sp_settriggerorder系统存储过程来改变这些触发器的执行次序.
sp_settriggerorder<triggername>,<order-value>,<DML-operation>
ordervalue = FIRST | LAST | NONEè随机顺序
DML-operation 指出创建触发器的DML操作.
sp_settriggerorder 'trgDeleteTitles' , 'FIRST', 'DELETE'
*********************************************************************
INSTEAD OF 触发器 替换的触发器
如:
create trigger trgPublisherDelete
on publishers
instead of delete
as
print 'Master records cannot be deleted!'
这种触发器在一张表上只能创建一个
不正确之处。。欢迎大家指正