日期:2014-05-17 浏览次数:20769 次
drop table a
drop table b
drop table c
go
create table b
(bid int identity(1,1) primary key,bdate datetime)
create table a
(aid int identity(1,1) primary key,
bid int foreign key references b(bid),
sta int,
adate datetime
)
create table c
(cid int identity(1,1) primary key,cdate datetime)
insert into b
select '2013-10-30 10:10:10' union all
select '2013-10-30 21:10:10'
insert into a
values(1,1,getdate())
create trigger dbo.trigger_a_insert
on dbo.a
for insert
as
if exists(select 1 from inserted i
inner join dbo.b
on i.bid = b.bid
where i.sta = 1 and b.bdate < getdate())
insert into c values(getdate())
go
--1.触发,c表插入1条记录
insert into a(bid,sta,adate) values(1,1,getdate())
select * from c
/*
cid cdate
1 2013-10-30 16:02:20.943
*/
--2.不触发,c表不会插入记录,还是1条
insert into a(bid,sta,adate) values(1,0,getdate())
select * from c
/*
cid cdate
1 2013-10-30 16:02:20.943
*/
--2.不触发,由于bid=2时,bdate大于当前时间,所以不插入,还是1条
insert into a(bid,sta,adate) values(2,1,getdate())
select * from c
/*
cid cdate
1 2013-10-30 16:02:20.943
*/