SQL 脚本四

 

 fristly,let's we see a picture:

 

 

 

触发器是在对表进行插入、更新或删除操作时自动执行的存储过程

触发器通常用于强制业务规则；是一种高级约束，可以定义比用CHECK 约束更为复杂的约束。 可执行复杂的SQL语句（if/while/case）引用其它表中的列

     触发器定义在特定的表上，与表相关
     自动触发执行
     不能直接调用
     是一个事务（可回滚）

触发器触发时：

#系统自动在内存中创建deleted表或inserted表

#只读，不允许修改；触发器执行完成后，自动删除
inserted 表
#临时保存了插入或更新后的记录行
#可以从inserted表中检查插入的数据是否满足业务需求
 # 如果不满足，则向用户报告错误消息，并回滚插入操作
deleted 表
#临时保存了删除或更新前的记录行
#可以从deleted表中检查被删除的数据是否满足业务需求
 # 如果不满足，则向用户报告错误消息，并回滚插入操作

 

语法：
CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR [DELETE, INSERT, UPDATE]
 AS
  T-SQL语句
GO
WITH ENCRYPTION表示加密触发器定义的SQL文本
DELETE, INSERT, UPDATE指定触发器的类型

 

//定义数据库
use classscore
go
if exists (select * from sysobjects where name='tranuser')
 drop table tranuser
if exists (select * from sysobjects where name='traninfo')
 drop table traninfo
go
create table tranuser
(
 ids int not null,
 username char(8) not null,
 moneys money not null
)
go
create table traninfo
(
 cid int not null,
 trantype char(4) not null,
 tranmoney money not null,
 dates datetime not null
)
go
alter table tranuser
 add constraint ck_money check(moneys>=1)
alter table traninfo
 add constraint df_dates default(getdate()) for dates
go
insert into tranuser(ids,username,moneys)values(1,'张三',1000)
insert into tranuser(ids,username,moneys)values(2,'李四',1)
insert into traninfo(cid,trantype,tranmoney)values(1,'支取',200)
select * from tranuser
select * from traninfo
go

 

INSERT触发器-问题：
解决上述的银行取款问题：当向交易信息表(transInfo)中插入一条交易信息时，我们应自动更新对应帐户的余额。

分析：
在交易信息表上创建INSERT触发器
从inserted临时表中获取插入的数据行
根据交易类型(transType)字段的值是存入/支取，
 增加/减少对应帐户的余额。

 

--定义insert触发器
if exists (select * from sysobjects where name='tg_traninfo')
 drop trigger tg_traninfo
go
create trigger tg_traninfo
on traninfo
for insert
as
 declare @uid int,@types char(4),@moneys money
 select @uid=cid,@types=trantype,@moneys=tranmoney
 from inserted
 if (@types='支取')
  update tranuser set moneys=moneys-@moneys where ids=@uid
 else
  update tranuser set moneys=moneys+@moneys where ids=@uid
go

--执行insert触发器
insert into traninfo(cid,trantype,tranmoney)values(1,'支取',300)
select * from tranuser
select * from traninfo

 

DELETE触发器-问题
问题：
当删除交易信息表时，要求自动备份被删除的数据到表backupTable中 。

分析：
在交易信息表上创建DELETE触发器
被删除的数据可以从deleted表中获取

 

--定义delete触发器
if exists(select * from sysobjects where name='tg_infodel')
 drop trigger tg_infodel
go
create trigger tg_infodel
on traninfo
for delete
as
 if not exists (select * from sysobjects where name='tranbak')
  select * into tranbak from deleted
 else
  insert into tranbak select * from deleted
go

--执行delete触发器
delete from traninfo where tranmoney=200
select * from tranbak

 

UPDATE触发器-问题
问题：
跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。

分析：
在bank表上创建UPDATE触发器
修改前的数据可以从deleted表中获取
修改后的数据可以从inserted表中获取 
               
--定义update触发器
if exists(select * from sysobjects where name='tg_infoup')
 drop trigger tg_infoup
go
create trigger tg_infoup
on tranuser
for update
as
 declare @old money,@new money
 select @old=moneys from deleted
 select @new=moneys from inserted
 if (abs(@new-@old)>20000)
 begin
  print '超过限额！交易失败！'
  rollback transaction
 end
go

--执行update触发器
update tranuser set moneys=moneys+30000 where ids=1
select * from tranuser

 

列级UPDATE触发器
UPDATE触发器除了跟踪数据的变化（修改）外，还可以检查是否修改了某列的数据
使用UPDATE（列）函数检测是否修改了某列

问题：
交易日期一般由系统自动产生，默认为当前日期。为了安全
起见，一般禁止修改，以防舞弊。

分析：
UPDATE（列名）函数可以检测是否修改了某列

 

--定义列级update触发器
if exists(select * from sysobjects where name='tg_infodate')
 drop trigger tg_infodate
go
create trigger tg_infodate
on traninfo
for update
as
 if update(dates)
 begin
  print '不能修改交易时间！'
  rollback transaction
 end
go

--执行列级update触发器
set nocount on --不显示受影响的行数
update traninfo set dates='2001-1-1 00:00:00'
select * from traninfo