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介绍
1.inserted是一张临时表
2.改表只有在触发器被执行时才起作用
3.inserted表结构是怎样的：它和被操作的表结果完全一致
*/
--如何删除触发器
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
--如何修改触发器
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
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
insert into tblrecordinfo
(intComputerId,intCardId,dtmStart,dtmEnd,mnyFee)
values
(2, 1, '2012-7-21', '2012-7-21', 2)
select * from tblrecordinfo
alter 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