代码如下: if (select count(1) from inserted i, PINV P, PODTL2 PD2 where P.PINV=i.PINV and PD2.PONUM =i.PO and PD2.LOT = i.LOT and abs(datediff(day,P.IDATE,PD2.DELDATE)) >5 ) > 0 begin raiserror('警告:收貨日期大于或小于PO交期5天!',18,18) end
你把需要保存的数据处理放raiserror前边。
这是在trigger里,不能放前面,还有很多其它判断
create table tb(id int,sname varchar(10)) insert into tb select 1,'a' union all select 2,'b' gocreate trigger update_tb on tb instead of insert,update as begin if exists (select 1 from deleted) begin raiserror('update!',16,1) update a set a.sname = b.sname from tb a join inserted b on a.id = b.id end else begin raiserror('insert!',16,1) insert into tb select id,sname from inserted end end go-- 1 insert into tb select 3,'c'select * from tb-- 2 update tb set sname = 'bb' where id = 3select * from tbdrop trigger update_tb drop table tb/***************************消息 50000,级别 16,状态 1,过程 update_tb,第 15 行 insert!(1 行受影响)(1 行受影响) id sname ----------- ---------- 1 a 2 b 3 c -- insert(3 行受影响)消息 50000,级别 16,状态 1,过程 update_tb,第 8 行 update!(1 行受影响)(1 行受影响) id sname ----------- ---------- 1 a 2 b 3 bb --update(3 行受影响)
if (select count(1) from inserted i, PINV P, PODTL2 PD2 where P.PINV=i.PINV and PD2.PONUM =i.PO and PD2.LOT = i.LOT and abs(datediff(day,P.IDATE,PD2.DELDATE)) >5 ) > 0
begin
raiserror('警告:收貨日期大于或小于PO交期5天!',18,18)
end
create table tb(id int,sname varchar(10))
insert into tb
select 1,'a' union all
select 2,'b'
gocreate trigger update_tb on tb
instead of insert,update
as
begin
if exists (select 1 from deleted)
begin
raiserror('update!',16,1)
update a
set a.sname = b.sname
from tb a join inserted b on a.id = b.id
end
else
begin
raiserror('insert!',16,1)
insert into tb
select id,sname from inserted
end
end
go-- 1
insert into tb
select 3,'c'select * from tb-- 2
update tb
set sname = 'bb'
where id = 3select * from tbdrop trigger update_tb
drop table tb/***************************消息 50000,级别 16,状态 1,过程 update_tb,第 15 行
insert!(1 行受影响)(1 行受影响)
id sname
----------- ----------
1 a
2 b
3 c -- insert(3 行受影响)消息 50000,级别 16,状态 1,过程 update_tb,第 8 行
update!(1 行受影响)(1 行受影响)
id sname
----------- ----------
1 a
2 b
3 bb --update(3 行受影响)