表tb1其中的两个字段x,y 表tb2的其中一个字段z
触发器要求:x>z-y 那么此条记录不能保存
create trigger tb1_iu on tb1 for insert,update
as
if exists(select 1 from inserted a join tb2 b on a.id=b.id
where x>z-y )
begin
raiserror('不能保存',16,1)
rollback
endcreate trigger tb2_iu on tb2 for insert,update
as
if exists(select 1 from inserted a join tb1 b on a.id=b.id
where x>z-y )
begin
raiserror('不能保存',16,1)
rollback
end
触发器要求:x>z-y 那么此条记录不能保存
create trigger tb1_iu on tb1 for insert,update
as
if exists(select 1 from inserted a join tb2 b on a.id=b.id
where x>z-y )
begin
raiserror('不能保存',16,1)
rollback
endcreate trigger tb2_iu on tb2 for insert,update
as
if exists(select 1 from inserted a join tb1 b on a.id=b.id
where x>z-y )
begin
raiserror('不能保存',16,1)
rollback
end
for insert
as
if exists(select 1 from inserted a join tb2 b on a.id=b.id where a.x>b.z-a.y)
rollback tran
go
for insert
as
if exists(select 1 from inserted a join tb2 b on a.id=b.id where a.x>b.z-a.y)
rollback tran
go
不要改掉我的参数形式!
CREATE TRIGGER t_insert
ON tb1
FOR INSERT
AS
DECLARE @x VARCHAR(15)
@y VARCHAR(15)
@z VARCHAR(15)SELECT @x=i.x,@y=i.y,@z=tb2.z
FROM inserted i join tb2 ON i.id=tb2.idIF @x>@z-@y
BEGIN
ROLLBACK TRAN
RAISERROR('can not insert',16,10)
END
GO
还有,我不明白上面的回答select 1 from ...这个1是什么意思?
ON tb1
FOR INSERT
ASif exists(SELECT 1 FROM inserted i join tb2 b ON i.id=tb2.id where i.x>i.y-b.z)
BEGIN
ROLLBACK TRAN
RAISERROR('can not insert',16,10)
END
GO
CREATE TRIGGER t_insert
ON tb1
FOR INSERT
ASif exists(SELECT 1 FROM inserted i join tb2 b ON i.id=b.id where i.x>i.y-b.z)
BEGIN
ROLLBACK TRAN
RAISERROR('can not insert',16,10)
END
GO
create table tb1(id int,x int,y int)
create table tb2(id int,z int)go
--创建触发器
CREATE TRIGGER t_insert
ON tb1
FOR INSERT
ASif exists(SELECT 1 FROM inserted i join tb2 b ON i.id=b.id where i.x>i.y-b.z)
BEGIN
ROLLBACK TRAN
RAISERROR('can not insert',16,10)
END
GO--初始化tb2
insert into tb2
select 1,10
union all select 2,20
union all select 3,30--插入数据测试
insert into tb1 select 1,2,20go
insert into tb1
select 1,1,2
select 1,10,20go
--显示结果
select * from tb1go
--删除测试环境
drop table tb1,tb2