表a结构为
create table a
(
id int identity pramery key,
lxName varchar(50) not null,
costEvidence varchar(100)
)costEvidence为凭证号,现在此表存在一部分数据,
要求新插入或修改的表a数据时不可以与CostEvidence现有数据重复,
这样的触发器怎么写呢
create table a
(
id int identity pramery key,
lxName varchar(50) not null,
costEvidence varchar(100)
)costEvidence为凭证号,现在此表存在一部分数据,
要求新插入或修改的表a数据时不可以与CostEvidence现有数据重复,
这样的触发器怎么写呢
CREATE TRIGGER TRI ON A FOR UPDATE,INSERTED
AS
BEGIN
IF EXISTS(SELECT 1 FROM A JOIN INSERTED I ON A.costEvidence=I.costEvidence )
处理..
ELSE
处理..
END
for insert,update
as
if ( Exists( select * from inserted as i join [RegBusiness] as r on i.CostEvidence = r.costEvidence ) )
begin
raiserror('缴费凭证号已经存在',16,1)
rollback transaction
end
消息 50000,级别 16,状态 1,过程 RegBusiness_I,第 8 行
缴费凭证号已经存在
消息 3609,级别 16,状态 1,第 1 行
事务在触发器中结束。批处理已中止。
你那个判断是事后的了,只要你一插入或UPDATE数据就会提示,甭管costEvidence已经存在。
ALTER trigger [RegBusiness_I] on [dbo].[RegBusiness]
instead of insert,update
as
if ( Exists( select * from inserted as i join [RegBusiness] as r on i.CostEvidence = r.costEvidence ) )
begin
raiserror('缴费凭证号已经存在',16,1)
rollback transaction
end
for insert,update
as
if ( Exists( select * from inserted as i join [RegBusiness] as r on i.CostEvidence = r.costEvidence
AND I.id<> R.ID ---加个条件,别被自己绊倒自己
) )
begin
raiserror('缴费凭证号已经存在',16,1)
rollback transaction
end
instead of insert,update
as
if ( Exists( select * from inserted as i join [RegBusiness] as r on i.CostEvidence = r.costEvidence ) )
begin
raiserror('缴费凭证号已经存在',16,1)
rollback transaction
return
end delete RegBusiness where id in (select id from deleted)
insert RegBusiness(lxName,costEvidence) select lxName,costEvidence from inserted
ALTER trigger [RegBusiness_I] on [dbo].[RegBusiness]
instead of insert,update
as
if ( Exists( select * from inserted as i join [RegBusiness] as r on i.CostEvidence = r.costEvidence ) )
begin
raiserror('缴费凭证号已经存在',16,1)
rollback transaction
end 这样写无论插入一条相同的CostEvidence或不同的都提示一条数据插入成功了,但实际上数据没有插入进去
缴费凭证号已经存在
消息 3609,级别 16,状态 1,第 1 行
事务在触发器中结束。批处理已中止。
ALTER trigger [RegBusiness_I] on [dbo].[RegBusiness]
instead of insert,update
as
if ( Exists( select * from inserted as i join [RegBusiness] as r on i.CostEvidence = r.costEvidence ) )
begin
raiserror('缴费凭证号已经存在',16,1)
rollback transaction
return
end
delete RegBusiness where id in (select id from deleted)
insert RegBusiness(lxName,costEvidence) select lxName,costEvidence from inserted