if object_id('tbTest') is not null
drop table tbTest
GO
CREATE TABLE [dbo].[tbTest] (
[person_id] [int] NULL ,
[zhiwu_id] [int] NULL,
[Person_biye] [bit] NULL,
[jiaojingkemuone] [int] NULL,
[jiaojingkemutwo] [int] NULL
) ON [PRIMARY]
GO
insert tbTest
select 1,1,1,1,1 union all
select 2,1,1,1,1 union all
select 3,1,1,1,1 union all
select 4,1,1,1,1 union all
select 5,1,1,1,1
GO
create trigger tr_update_tbTest
on tbTest
for update
as
update a set Person_biye=0
from tbTest a,inserted i,deleted d
where a.Person_id=i.Person_id
and i.Person_id=d.Person_id
and (i.jiaojingkemuone=0 and d.jiaojingkemuone=1 or
i.jiaojingkemutwo=0 and d.jiaojingkemutwo=1)
GO
----更新
update tbTest set jiaojingkemuone = 0 where Person_id = 2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[BM_person_zhiwu]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[BM_person_zhiwu]
GO
CREATE TABLE [dbo].[BM_person_zhiwu] (
[person_id] [int] NULL ,
[zhiwu_id] [int] NULL
) ON [PRIMARY]
GO
数据就很简单了 填一条两条 就行了 谢谢你 哥们
create trigger tr_BM_xueyuan1_update
on BM_xueyuan
for update
as
update a set
Person_biye=0
from BM_xueyuan a,inserted i,deleted d
where a.Person_id=i.Person_id
and i.Person_id=d.Person_id
and (i.jiaojingkemuone=0 and d.jiaojingkemuone=1 or
i.jiaojingkemutwo=0 and d.jiaojingkemutwo=1 or
i.jiaojingkemutree=0 and d.jiaojingkemutree=1 or
i.jiaotongjuone=0 and d.jiaotongjuone=1 or
i.jiaotongjutwo=0 and d.jiaotongjutwo=1 or
i.jiashizheng=0 and d.jiashizheng=1 or
i.jieyezheng=0 and d.jieyezheng=1 )
这样吗 没有语法错误了 可是 不好使啊 没有达到预期效果
create trigger tr_BM_xueyuan1_update
on BM_xueyuan1
for update
as
update a
Person_biye=0
from BM_xueyuan a,inserted i,deleted d
where a.Person_id=i.Person_id
and i.Person_id=d.Person_id
and (i.jiaojingkemuone=0 and d.jiaojingkemuone=1 or
i.jiaojingkemutwo=0 and d.jiaojingkemutwo=1 or
i.jiaojingkemutree=0 and d.jiaojingkemutree=1 or
i.jiaotongjuone=0 and d.jiaotongjuone=1 or
i.jiaotongjutwo=0 and d.jiaotongjutwo=1 or
i.jiashizheng=0 and d.jiashizheng=1 or
i.jieyezheng=0 and d.jieyezheng=1 )
我这样写的 说
Person_biye=0 这是第七行 有错误
create trigger tr_BM_xueyuan_update
on BM_xueyuan
for update
as
update a
Person_biye=0
from BM_xueyuan a,inserted i,deleted d
where a.Person_id=i.Person_id
and i.Person_id=d.Person_id
and (i.jiaojingkemuone=0 and d.jiaojingkemuone=1 or
i.jiaojingkemutwo=0 and d.jiaojingkemutwo=1 or
i.jiaojingkemutree=0 and d.jiaojingkemutree=1 or
i.jiaotongjuone=0 and d.jiaotongjuone=1 or
i.jiaotongjutwo=0 and d.jiaotongjutwo=1 or
i.jiashizheng=0 and d.jiashizheng=1 or
i.jieyezheng=0 and d.jieyezheng=1 )