Update LinkMan Set LinkManName=(select LinkManName from inserted where LinkMan.ClientID=inserted.ClientID)
以前我也经常遇到这样的问题,后来我总结出几个发生这样错误的情况: 1.在触发器表中使用诸如 select * from 触发器表名,也就是从触发器表中选记录。 2.遇到一些值为null的错误,比如你的@ClientID 没有初始化.试试set @clientID=''
但是你的@clientID 好像没有用呀??
--我在我这里模拟你的表,触发器没有问题 --建表 create table Client(ClientID varchar(5) PRIMARY KEY,ClientName varchar(20),LinkManName varchar(20)) go create table LinkMan(Code int identity(1,1) PRIMARY KEY,ClientID varchar(5) NOT NULL DEFAULT ('') references Client(ClientID),LinkManName varchar(20)) go --建触发器 create Trigger LinkManTrig on Client for update as if update(LinkManName) Update b Set b.LinkManName=a.LinkManName from inserted a ,LinkMan b where a.ClientID=b.ClientID go --插入数据 insert into Client select '00001','王二','小王' union all select '00002','张三','小张' union all select '00003','李四','小李' union all select '00004','赵五','小赵' goinsert into LinkMan(ClientID,LinkManName) select '00001','小王' union all select '00002','小张' union all select '00003','小李' union all select '00004','小赵' go --初始数据 select * from Clientselect * from LinkMan --结果 /* ClientID ClientName LinkManName -------- -------------------- -------------------- 00001 王二 小王 00002 张三 小张 00003 李四 小李 00004 赵五 小赵(所影响的行数为 4 行)Code ClientID LinkManName ----------- -------- -------------------- 1 00001 小王 2 00002 小张 3 00003 小李 4 00004 小赵(所影响的行数为 4 行) */ --更新Client update Client set LinkManName='小丁' where ClientID='00003'--查看结果,看触发器是否起作用 select * from Clientselect * from LinkMan--查看结果,触发器起了作用,LINKMAN表已经更新 /* (所影响的行数为 1 行) (所影响的行数为 1 行)ClientID ClientName LinkManName -------- -------------------- -------------------- 00001 王二 小王 00002 张三 小张 00003 李四 小丁 00004 赵五 小赵(所影响的行数为 4 行)Code ClientID LinkManName ----------- -------- -------------------- 1 00001 小王 2 00002 小张 3 00003 小丁 4 00004 小赵(所影响的行数为 4 行) */
xiaoliaoyun(流浪的云)的方法可以了,不会有问题了。
-- xiaoliaoyun(流浪的云) 的方法还少了一点,如果更新ClientID就有问题了 create Trigger LinkManTrig on Client for update as if update(LinkManName) or update(ClientID) begin select id=identity(int,1,1),ClientID,LinkManName into #i from inserted where isnull(LinkManName,'')<>'' select id=identity(int,1,1),ClientID into #d from deleted Update LinkMan set ClientID=b.i_ClientID,LinkManName=b.LinkManName from LinkMan a join( select i_ClientID=i.ClientID,d_ClientID=d.ClientID,LinkManName from #i i join #d d on i.id=d.id )b on a.ClientID=b.d_ClientID end go
to:zjcxc(: 邹建 :) 根本就不能更新ClientID字段。--建表 create table Client(ClientID varchar(5) PRIMARY KEY,ClientName varchar(20),LinkManName varchar(20)) go create table LinkMan(Code int identity(1,1) PRIMARY KEY,ClientID varchar(5) NOT NULL DEFAULT ('') references Client(ClientID),LinkManName varchar(20)) go --建触发器 create Trigger LinkManTrig on Client for update as if update(LinkManName) or update(ClientID) begin select id=identity(int,1,1),ClientID,LinkManName into #i from inserted where isnull(LinkManName,'')<>''
select id=identity(int,1,1),ClientID into #d from deleted Update LinkMan set ClientID=b.i_ClientID,LinkManName=b.LinkManName from LinkMan a join( select i_ClientID=i.ClientID,d_ClientID=d.ClientID,LinkManName from #i i join #d d on i.id=d.id )b on a.ClientID=b.d_ClientID end go--插入数据 insert into Client select '00001','王二','小王' union all select '00002','张三','小张' union all select '00003','李四','小李' union all select '00004','赵五','小赵' goinsert into LinkMan(ClientID,LinkManName) select '00001','小王' union all select '00002','小张' union all select '00003','小李' union all select '00004','小赵' go --初始数据 select * from Clientselect * from LinkMan --更新Client update Client set LinkManName=null where ClientID='00003' update Client set clientid='00008' where ClientID='00004'--查看结果,看触发器是否起作用 select * from Clientselect * from LinkMan drop table linkman,clientgo
CREATE Trigger T_UpdateLinkMan on Client
for update
as
Update b
Set b.LinkManName=a.LinkManName
from inserted a ,LinkMan b
where a.ClientID=b.ClientID
[email protected]
1.在触发器表中使用诸如 select * from 触发器表名,也就是从触发器表中选记录。
2.遇到一些值为null的错误,比如你的@ClientID 没有初始化.试试set @clientID=''
--建表
create table Client(ClientID varchar(5) PRIMARY KEY,ClientName varchar(20),LinkManName varchar(20))
go
create table LinkMan(Code int identity(1,1) PRIMARY KEY,ClientID varchar(5) NOT NULL DEFAULT ('') references Client(ClientID),LinkManName varchar(20))
go
--建触发器
create Trigger LinkManTrig on Client
for update
as
if update(LinkManName)
Update b
Set b.LinkManName=a.LinkManName
from inserted a ,LinkMan b
where a.ClientID=b.ClientID
go
--插入数据
insert into Client
select '00001','王二','小王' union all
select '00002','张三','小张' union all
select '00003','李四','小李' union all
select '00004','赵五','小赵'
goinsert into LinkMan(ClientID,LinkManName)
select '00001','小王' union all
select '00002','小张' union all
select '00003','小李' union all
select '00004','小赵'
go
--初始数据
select * from Clientselect * from LinkMan
--结果
/*
ClientID ClientName LinkManName
-------- -------------------- --------------------
00001 王二 小王
00002 张三 小张
00003 李四 小李
00004 赵五 小赵(所影响的行数为 4 行)Code ClientID LinkManName
----------- -------- --------------------
1 00001 小王
2 00002 小张
3 00003 小李
4 00004 小赵(所影响的行数为 4 行)
*/
--更新Client
update Client
set LinkManName='小丁'
where ClientID='00003'--查看结果,看触发器是否起作用
select * from Clientselect * from LinkMan--查看结果,触发器起了作用,LINKMAN表已经更新
/*
(所影响的行数为 1 行)
(所影响的行数为 1 行)ClientID ClientName LinkManName
-------- -------------------- --------------------
00001 王二 小王
00002 张三 小张
00003 李四 小丁
00004 赵五 小赵(所影响的行数为 4 行)Code ClientID LinkManName
----------- -------- --------------------
1 00001 小王
2 00002 小张
3 00003 小丁
4 00004 小赵(所影响的行数为 4 行)
*/
create Trigger LinkManTrig on Client
for update
as
if update(LinkManName) or update(ClientID)
begin
select id=identity(int,1,1),ClientID,LinkManName into #i from inserted where isnull(LinkManName,'')<>''
select id=identity(int,1,1),ClientID into #d from deleted Update LinkMan set ClientID=b.i_ClientID,LinkManName=b.LinkManName
from LinkMan a join(
select i_ClientID=i.ClientID,d_ClientID=d.ClientID,LinkManName
from #i i join #d d on i.id=d.id
)b on a.ClientID=b.d_ClientID
end
go
根本就不能更新ClientID字段。--建表
create table Client(ClientID varchar(5) PRIMARY KEY,ClientName varchar(20),LinkManName varchar(20))
go
create table LinkMan(Code int identity(1,1) PRIMARY KEY,ClientID varchar(5) NOT NULL DEFAULT ('') references Client(ClientID),LinkManName varchar(20))
go
--建触发器
create Trigger LinkManTrig on Client
for update
as
if update(LinkManName) or update(ClientID)
begin
select id=identity(int,1,1),ClientID,LinkManName into #i from inserted where isnull(LinkManName,'')<>''
select id=identity(int,1,1),ClientID into #d from deleted
Update LinkMan set ClientID=b.i_ClientID,LinkManName=b.LinkManName
from LinkMan a join(
select i_ClientID=i.ClientID,d_ClientID=d.ClientID,LinkManName
from #i i join #d d on i.id=d.id
)b on a.ClientID=b.d_ClientID
end
go--插入数据
insert into Client
select '00001','王二','小王' union all
select '00002','张三','小张' union all
select '00003','李四','小李' union all
select '00004','赵五','小赵'
goinsert into LinkMan(ClientID,LinkManName)
select '00001','小王' union all
select '00002','小张' union all
select '00003','小李' union all
select '00004','小赵'
go
--初始数据
select * from Clientselect * from LinkMan
--更新Client
update Client
set LinkManName=null
where ClientID='00003'
update Client
set clientid='00008'
where ClientID='00004'--查看结果,看触发器是否起作用
select * from Clientselect * from LinkMan
drop table linkman,clientgo
UPDATE 语句与 COLUMN REFERENCE 约束 'FK__LinkMan__ClientI__7CC477D0' 冲突。该冲突发生于数据库 'pubs',表 'LinkMan', column 'ClientID'。
语句已终止。
如果确实有外键约束,当然我的考虑就是多余的了.