触发器代码,给你参考
if exists(select * from sysobjects where name='trig_insert')
drop trigger trig_insert
go
--创建插入触发器
create trigger trig_insert
on b表
for insert
as
declare @aid float,@bnum float,@tnum float
select @aid=aid,@bnum=bnum from inserted
select @tnum=sum(bnum)+@bnum from b表 where aid=@aid
if(@tnum>100)
begin
raiserror('bnum超过100,插入取消',16,1);
rollback tran
return
end
go
if exists(select * from sysobjects where name='trig_insert')
drop trigger trig_insert
go
--创建插入触发器
create trigger trig_insert
on b表
for insert
as
declare @aid float,@bnum float,@tnum float
select @aid=aid,@bnum=bnum from inserted
select @tnum=sum(bnum)+@bnum from b表 where aid=@aid
if(@tnum>100)
begin
raiserror('bnum超过100,插入取消',16,1);
rollback tran
return
end
go
drop table A;
go
create table A (aid int primary key);
go
insert into A values(1),(3),(5),(6),(7);
goif object_id('B') is not null
drop table B;
go
create table B (bid int identity, aid int references [A](aid), bnum numeric(5,2));
go
insert into B (aid,bnum)
values(3,23.1),(3,1.23),(1,2.5),(3,2),(1,97.5),(6,99.99);
go-- 为了方便并发性处理,使用存储过程插入;缺点是,一次只能插入一条记录。
if object_id('dbo.usp_insertB') is not null
drop procedure dbo.usp_insertB;
go
create procedure dbo.usp_insertB
@aid int, @bnum numeric(5,2)
as
begin try
begin tran;
if (select sum(bnum)+@bnum from B with(updlock) where aid=@aid)>100
raiserror(50001,16,1)
insert into B(aid,bnum) values(@aid,@bnum);
-- 可以设置延时,测试并发性
-- waitfor delay '00:00:05';
commit tran;
end try
begin catch
raiserror('Deny inserting due to the sum of bnums beyond 100.',16,1);
rollback tran;
end catch
go-- 在存储过程中设置延时后,可以在多个会话中同时执行此条语句,最后结果只有一条记录被插入
exec dbo.usp_insertB 6,0.01;select * from B;
-- 这个是触发器的方法
if object_id('trg_insert_B') is not null
drop trigger trg_insert_B;
go
create trigger trg_insert_B on B
instead of insert
as
-- 可以设置延时,测试并发性
-- waitfor delay '00:00:05';
if not exists (select aid from (
select aid,bnum from B with(updlock) -- 设置 updlock 锁,串行化插入操作
where aid in (select aid from inserted)
union all
select aid,bnum from inserted) t
group by aid having sum(bnum)>100)
insert into B (aid,bnum) select aid,bnum from inserted;
else
raiserror('Deny inserting due to the sum of bnums beyond 100.',16,1);
goinsert into B (aid,bnum) values(6,0.01);