22,209
社区成员
发帖
与我相关
我的任务
分享
declare @t table(a int,b varchar(20),c datetime,d money,e as case when isnull(a+b+c+d,0)=0 then 0 else 1 end)
--给D列添加默认值
alter table tb add constraint DF_D default 0 for D
go
--创建触发器
create trigger tri_tb on tb
for insert,update
as
if update(D)
rollback --不允许手动设置
update tb
set D=0
from inserted i
where tb.id=i.id and (i.A is null or i.B is null or i.C is null)
update tb
set D=1
from inserted i
where tb.id=i.id and (i.A is not null and i.B is not null and i.C is not null)
go
create table test1(a int,b varchar(20),c smalldatetime,d as case WHEN a IS NULL OR b IS NULL OR c IS NULL then 0 else 1 end)
INSERT test1 SELECT NULL,'fas',GETDATE()
INSERT test1 SELECT 1,'fas',GETDATE()
SELECT * FROM test1
--result
/*a b c d
----------- -------------------- ------------------------------------------------------ -----------
NULL fas 2009-03-12 10:50:00 0
1 fas 2009-03-12 10:50:00 1
(所影响的行数为 2 行)
*/