create trigger tr_tablea_update
on tablea
for updateif update(aa)
begin
if exists (select * from inserted where aa is null)
begin
RAISERROR ('不能修改AA为空',16,1)
ROLLBACK TRANSACTION
end
end
go
on tablea
for updateif update(aa)
begin
if exists (select * from inserted where aa is null)
begin
RAISERROR ('不能修改AA为空',16,1)
ROLLBACK TRANSACTION
end
end
go
一开始我也是这么想,后来想可能他插入时允许为空.
在关键字 'if' 附近有语法错误。
你用 ROLLBACK TRANSACTION ,则bb不就更新不了了。
不过用户可能没这个需求。
on tablea
for update
as
if update(aa)
begin
if exists (select * from inserted where aa is null)
begin
RAISERROR ('不能修改AA为空',16,1)
ROLLBACK TRANSACTION
end
end
goto:pausing(select 人生 from data)
原来有NULL不是问题,因为判断了UPDATE(AA),除非你的UPDATE语句是:
UPDATE TABLEA SET AA=NULL .....才会在INSERTED表得到有NULL的记录,但这种情况是应该回滚的to: hzm_8(三儿)
你说的情况也应该回滚!
那我一次update 多个栏位,由于事务的回滚,其他的栏位没有被update 怎么解决?
[aa] [char] (10) NULL ,
[bb] [int] NOT NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[TABLE1] WITH NOCHECK ADD
CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED
(
[bb]
) ON [PRIMARY]
GO
CREATE TRIGGER TRTest ON dbo.TABLE1
FOR update
AS
set nocount on
if update(aa)
begin
declare @cnt int
select @cnt=count(*) from inserted
if @cnt>0
begin
declare @aaold char(10),@aanew char(10),@bbnew int
declare crinsert cursor local for select aa,bb From Inserted
declare crdelete cursor local for select aa From deleted
open crinsert
open crdelete
fetch next from crinsert into @aanew,@bbnew
fetch next from crdelete into @aaold while @@fetch_status=0
begin
if (@aanew is null) and (@aaold is not null)
update table1 set aa=@aaold where bb=@bbnew
fetch next from crinsert into @aanew,@bbnew
fetch next from crdelete into @aaold
end
close crinsert
deallocate crinsert
close crdelete
deallocate crdelete
end
end
GOinsert into table1(aa,bb) values('test1',1)
insert into table1(aa,bb) values('test2',2)
insert into table1(aa,bb) values(null,3)
update table1 set aa=null,bb=bb+1
select * from table1执行结果:(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 1 行)
(所影响的行数为 3 行)aa bb
---------- -----------
test1 2
test2 3
NULL 4(所影响的行数为 3 行)
on tablea
for update
as
if update(aa)
begin
update a
set aa=d.aa
from tablea a,inserted i,deleted d
where a.key=i.key and a.key=d.key
and i.aa is null
end
go