SQLServer触发器

--打开netbar数据库
use netbar
go
--请判断下面的两条sql语句来业务逻辑上是否合理
--第一条：新增会员卡，会员卡开通时间：2012-7-1
insert into tblcardinfo
(chvUserName,chvPassword,mnyBalance,dtmRegisterTime)
values
('dujiu', '123456', 50, '2012-7-1')
go
select * from tblcardinfo;
--第二条：新增上机记录，其中开始上机时间：2012-5-1
insert into tblrecordinfo
(intComputerId,intCardId,dtmStart,dtmEnd,mnyFee)
values
(1, 4, '2012-5-1', '2012-5-1', 2)
go
select * from tblrecordinfo;
go
--虽然上面两条语句都能执行成功，但是错误的业务逻辑也很明显：
--2012年开通的会员卡怎么可能存在2012-5-1的上机记录呢？？？

--使用我们之前学过的检查约束，看能够解决这个问题

alter table tblrecordinfo
add constraint ck_recordinfo_dtmStart check (dtmStart>TblCardInfo.dtmRegisterTime)

--上面添加的检查约束无法被创建
--原因是：检查约束中被检查的字段只能来源于当前表，而不能从其他表中判断
--我们前面学过的任何约束都无法实现这一功能性约束！！！
--对于这种问题，我们的解决办法就是：触发器

 

 

-----------触发器-------------------------

--待解决的问题：向tblrecordinfo表中新增记录时，上机开始时间必须要大于会员卡注册时间


--
/*
  inserted和updated介绍
  1.它们都是临时表
  2.它们只有在触发器被执行时才存在
  3.它们的表结构和和触发器关联的表表结构完全一致
  4.inserted表存放insert或update语句执行所影响的表的副本
    deleted表存放delete或update语句执行所影响的表的副本
    两者的具体差别请参见下图
 
  ______________________________________________________________
  |                           |                              |                               |
  | 激活触发器的动作      |  Inserted表              |  Deleted表                |
  |__________________|____________________|_____________________|
  |                           |                              |                               |
  |  Insert                 | 存放要插入的记录         |                               |
  |__________________|____________________|_____________________|
  |                           |                              |                               |
  |  Update               | 存放要更新的记录         |  存放更新前的旧记录       |
  |__________________|____________________|_____________________|
  |                           |                              |                               |
  |  Delete                |                              |  存放要删除的旧记录       |
  |__________________|____________________|_____________________| 
*/

--判断触发器是否存在，存在则删除触发器
if exists (select * from sys.sysobjects where name = 'tr_insertRecord')
  drop trigger tr_insertRecord
go
--如何定义|创建一个触发器
create trigger tr_insertRecord
on TblRecordInfo
for insert--for等价于after,表示当新增完记录之后才会执行触发器
as
begin
  declare @cardid int, @startTime datetime;
  select @cardid = intcardid, @startTime = dtmStart from inserted;
  select '卡号：'+convert(nvarchar(5), @cardid);
  select '上机开始时间：'+convert(nvarchar(20), @startTime);
end
go
--如何修改触发器
alter trigger tr_insertRecord
on TblRecordInfo
for insert
as
begin
  --定义变量：会员卡号、开始上机时间、会员卡注册时间
  declare @cardid int, @startTime datetime, @registerTime datetime;
  --从inserted表中提取会员卡号、开始上机时间。此时的inserted表结构和TblRecordInfo一致
  select @cardid = intcardid, @startTime = dtmStart from inserted;
  --根据会员卡id从会员卡信息表中提取该卡的注册时间
  select @registerTime = dtmRegisterTime from tblcardInfo where intcardid=@cardid;
  --比较会员卡注册时间和上机时间
  if(@registerTime>@startTime)
  begin
    --不满足时进行事务回滚：使insert into tblrecord......无法真正被执行
    rollback transaction
  end
end

--使用insert向tblrecordinfo新增记录，验证触发器是否会执行
insert into tblrecordinfo
(intComputerId,intCardId,dtmStart,dtmEnd,mnyFee)
values
(1, 4, '2012-7-15', '2012-7-15', 2)

select * from tblcardinfo
go

select * from tblrecordinfo
go

 

 

--当我们新增上机记录时，必须同时手动修改对应电脑的使用状态
insert into tblrecordinfo
(intComputerId,intCardId,dtmStart,dtmEnd,mnyFee)
values
(1, 4, '2012-7-15', '2012-7-15', 2)
go
update tblcardinfo set intinuse = 1
where intcomputerid = 1
go
--通过触发器实现新增上机记录时，自动修改对应电脑的使用状态
--李红艳小红花一枚
create trigger tr_tblCardInfo
on TblRecordInfo
for insert
as
begin
  declare @computerId int;
  select @computerId = intcomputerid from inserted
  update tblcomputer set intinuse = 1 where intcomputerid=@computerid;
end

select * from tblcomputer
select * from tblcardinfo
select * from tblrecordinfo
go
insert into tblrecordinfo
(intComputerId,intCardId,dtmStart,dtmEnd,mnyFee)
values
(2, 1, '2012-7-15', '2012-7-15', 2)


--删除指定计算机时，同时将其上机记录删除掉.用触发器来完成
delete from tblcomputer where intcomputerid=1;
--郭洁、游长江：小红花
--王正义：臭鸡蛋
alter trigger tr_deletecomputer
on tblcomputer
--for delete
instead of delete
as
begin
  declare @computerid int;
  select @computerid=intcomputerid from deleted;
  delete from tblrecordinfo where intcomputerid = @computerid;
  delete from tblcomputer where intcomputerid = @computerid;
end

delete from tblcomputer where intcomputerid=1;

--张三换电脑：2-------->3
--我们需要修改之前添加的上机记录信息表中关于计算机id的列信息
--但同时我们又要修改两台计算机的使用状态
insert into tblrecordinfo
(intComputerId,intCardId,dtmStart,dtmEnd,mnyFee)
values
(2, 1, '2012-7-21', '2012-7-21', 2)

select * from tblrecordinfo

 

create trigger tr_updaterecordinfo
on tblrecordinfo
for update
as
begin
  declare @oldcomputerid int, @newcomputerid int;
  if update(intcomputerid)
  begin
    select @oldcomputerid = intcomputerid from deleted;
    select @newcomputerid = intcomputerid from inserted;
    update tblcomputer set intinuse = 1 where intcomputerid =@newcomputerid
    update tblcomputer set intinuse = 0 where intcomputerid =@oldcomputerid
  end
end

select * from tblcomputer
update tblrecordinfo set intcomputerid = 3 where intrecordid=19
select * from tblcomputer