大概 create trigger tr_tablename_update on tablename for update asupdate a 字段7=也跟着变化 from tablename a,inserted i,deleted d where a.id=i.id and i.id=d.id and (i.字段1=0 and d.字段1=1 or i.字段2=0 and d.字段2=1 or i.字段3=0 and d.字段3=1 or i.字段4=0 and d.字段4=1 or i.字段5=0 and d.字段5=1 or i.字段6=0 and d.字段6=1 )go
哦,那它如果原来不是1呢create trigger tr_tablename_update on tablename for update asupdate a 字段7=0 from tablename a,inserted i,deleted d where a.id=i.id and i.id=d.id and (i.字段1=0 and d.字段1=1 or i.字段2=0 and d.字段2=1 or i.字段3=0 and d.字段3=1 or i.字段4=0 and d.字段4=1 or i.字段5=0 and d.字段5=1 or i.字段6=0 and d.字段6=1 ) and a.字段7=1 go
create trigger tr_tablename_update on tablename for update asupdate a 字段7=0 from tablename a,inserted i,deleted d where a.id=i.id and i.id=d.id and (i.字段1=0 and d.字段1=1 or i.字段2=0 and d.字段2=1 or i.字段3=0 and d.字段3=1 or i.字段4=0 and d.字段4=1 or i.字段5=0 and d.字段5=1 or i.字段6=0 and d.字段6=1 ) and a.字段7=1 go
create trigger tr_BM_xueyuan_update on BM_xueyuan for update asupdate a Person_biye=0 from tablename 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 )go 我这么改的 帮我看看 哪里错了 报错啊
把tablename改成BM_xueyuancreate trigger tr_BM_xueyuan_update on BM_xueyuan for update asupdate 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 )
create trigger tr_BM_xueyuan1_update on BM_xueyuan1 for update asupdate 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 这是第七行 有错误
update a Person_biye=0 ------------------------------------------ update a SET --却个set Person_biye=0
create trigger tr_BM_xueyuan1_update on BM_xueyuan for update asupdate 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 ) 这样吗 没有语法错误了 可是 不好使啊 没有达到预期效果
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] GOCREATE TABLE [dbo].[BM_person_zhiwu] ( [person_id] [int] NULL , [zhiwu_id] [int] NULL ) ON [PRIMARY] GO 数据就很简单了 填一条两条 就行了 谢谢你 哥们
update BM_xueyuan set jiaojingkemuone=0 --要求jiaojingkemuone原来是1的 where Person_id=1再 select * from BM_xueyuan where Person_id=1
触发器代码没问题,以下在我的机器上成功执行: 环境:windows2003 sp1 + SQLSERVER2000sp4if 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----查看 select * from tbTest----清除测试环境 drop table tbTest/*结果(person_id=2的Person_biye已经被成功更新为0) person_id zhiwu_id Person_biye jiaojingkemuone jiaojingkemutwo ----------- ----------- ----------- --------------- --------------- 1 1 1 1 1 2 1 0 0 1 3 1 1 1 1 4 1 1 1 1 5 1 1 1 1 */
create trigger tr_tablename_update
on tablename
for update
asupdate a
字段7=也跟着变化
from tablename a,inserted i,deleted d
where a.id=i.id
and i.id=d.id
and (i.字段1=0 and d.字段1=1 or
i.字段2=0 and d.字段2=1 or
i.字段3=0 and d.字段3=1 or
i.字段4=0 and d.字段4=1 or
i.字段5=0 and d.字段5=1 or
i.字段6=0 and d.字段6=1 )go
on tablename
for update
asupdate a
字段7=0
from tablename a,inserted i,deleted d
where a.id=i.id
and i.id=d.id
and (i.字段1=0 and d.字段1=1 or
i.字段2=0 and d.字段2=1 or
i.字段3=0 and d.字段3=1 or
i.字段4=0 and d.字段4=1 or
i.字段5=0 and d.字段5=1 or
i.字段6=0 and d.字段6=1 )
and a.字段7=1
go
on tablename
for update
asupdate a
字段7=0
from tablename a,inserted i,deleted d
where a.id=i.id
and i.id=d.id
and (i.字段1=0 and d.字段1=1 or
i.字段2=0 and d.字段2=1 or
i.字段3=0 and d.字段3=1 or
i.字段4=0 and d.字段4=1 or
i.字段5=0 and d.字段5=1 or
i.字段6=0 and d.字段6=1 )
and a.字段7=1
go
on BM_xueyuan
for update
asupdate a
Person_biye=0
from tablename 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 )go
我这么改的 帮我看看 哪里错了 报错啊
on BM_xueyuan
for update
asupdate 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 )
on BM_xueyuan1
for update
asupdate 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 这是第七行 有错误
Person_biye=0
------------------------------------------
update a SET --却个set
Person_biye=0
create trigger tr_BM_xueyuan1_update
on BM_xueyuan
for update
asupdate 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 )
这样吗 没有语法错误了 可是 不好使啊 没有达到预期效果
drop table [dbo].[BM_person_zhiwu]
GOCREATE TABLE [dbo].[BM_person_zhiwu] (
[person_id] [int] NULL ,
[zhiwu_id] [int] NULL
) ON [PRIMARY]
GO
数据就很简单了 填一条两条 就行了 谢谢你 哥们
where Person_id=1再
select * from BM_xueyuan
where Person_id=1
环境:windows2003 sp1 + SQLSERVER2000sp4if 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----查看
select * from tbTest----清除测试环境
drop table tbTest/*结果(person_id=2的Person_biye已经被成功更新为0)
person_id zhiwu_id Person_biye jiaojingkemuone jiaojingkemutwo
----------- ----------- ----------- --------------- ---------------
1 1 1 1 1
2 1 0 0 1
3 1 1 1 1
4 1 1 1 1
5 1 1 1 1
*/
你的SQLSERVER打补丁了吗?