我有两个表,结构如下:
table1:
id int,//主键
name varchar(20),
addr int, // table2的外键
table2:
id int,//主键
info varchar(100), 现在我为了实现修改table2时自动更新table1中addr的值,使用触发器
CREATE TRIGGER Trigger1
ON table2
FOR UPDATE
AS
UPDATE table1
SET addr=i.id
FROM INSERTED i
WHERE table1.addr=i.id
END 然后我通过下面的SQL修改表中的内容
PDATE table2
SET id = 2008
WHERE (info= 'value')
时出现两种情况
如果table1中没有数据引用到table2中的数据,则成功
如果table1中有数据引用到table2中的数据,则失败,错误提示说两个表之间有关系,可是我在触发器中作了处理,为什么还不行呢,是不是这种方式不行,那么应该用什么方式呢?
table1:
id int,//主键
name varchar(20),
addr int, // table2的外键
table2:
id int,//主键
info varchar(100), 现在我为了实现修改table2时自动更新table1中addr的值,使用触发器
CREATE TRIGGER Trigger1
ON table2
FOR UPDATE
AS
UPDATE table1
SET addr=i.id
FROM INSERTED i
WHERE table1.addr=i.id
END 然后我通过下面的SQL修改表中的内容
PDATE table2
SET id = 2008
WHERE (info= 'value')
时出现两种情况
如果table1中没有数据引用到table2中的数据,则成功
如果table1中有数据引用到table2中的数据,则失败,错误提示说两个表之间有关系,可是我在触发器中作了处理,为什么还不行呢,是不是这种方式不行,那么应该用什么方式呢?
ON table2
FOR UPDATE
AS
UPDATE table1
SET addr=i.id
FROM INSERTED i JOIN TABLE1
ON table1.addr=i.id
END
这样试试看?
SET ID = 101
WHERE (ID = 1)
上面是我的语句,下面是错误
---------------------------
Microsoft Visual Studio
---------------------------
SQL Execution Error.Executed SQL statement: UPDATE Table2 SET ID = 101 WHERE (ID = 1)
Error Source: .Net SqlClient Data Provider
Error Message: The UPDATE statement conflicted with the REFERENCE constraint "FK_Table1_Table2". The conflict occurred in database "E:\MYPROJ\C#\GUESSGAME\SIMPLEDATA.MDF", table "dbo.Table1", column 'Addr'.The statement has been terminated.
CREATE TRIGGER Trigger1
ON table2
FOR UPDATE
AS
UPDATE table1
SET addr=i.id
FROM deleted i
WHERE table1.addr=i.id
END
ON table2
FOR UPDATE
AS
UPDATE table1
SET addr=d.id
FROM deleted i ,inserted d
WHERE table1.addr=i.id
END
试试看
才转入数据库不久,望各位不吝指教。
1 1 你
2 2 我
Table2中的数据
1 西大街
2 东大街
3 北大街Update Table2 Set ID=103 Where ID=3 //可以正确执行
Update Table2 Set ID=101 Where ID=1 //出现下面错误
---------------------------
Microsoft Visual Studio
---------------------------
SQL Execution Error.Executed SQL statement: UPDATE Table2 SET ID = 101 WHERE (ID = 1)
Error Source: .Net SqlClient Data Provider
Error Message: The UPDATE statement conflicted with the REFERENCE constraint "FK_Table1_Table2". The conflict occurred in database "E:\MYPROJ\C#\GUESSGAME\SIMPLEDATA.MDF", table "dbo.Table1", column 'Addr'.The statement has been terminated.
--1 把你表上的外键关系给删除
alter table table1 drop 约束名
--2 z再用上面的触发器。。CREATE TRIGGER Trigger1
ON table2
FOR UPDATE
AS
UPDATE table1
SET addr=d.id
FROM deleted i ,inserted d
WHERE table1.addr=i.id
END
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( id int primary key ,name varchar(20),addr int references tb2(id))
go
insert tb1 SELECT 1,'a',1 union all select 2,'b',2
go
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2( id int primary key ,info varchar(10))
insert tb2 SELECT 1,'value' union all select 2,'vl'
go
CREATE TRIGGER Trigger1
ON tB2
FOR UPDATE
AS
BEGIN
UPDATE tB1
SET addr=d.id
FROM deleted i ,inserted d
WHERE addr=i.id
END SELECT * FROM TB1
SELECT * FROM TB2UPDATE tB2
SET id = 2008
WHERE (info= 'value')
/*
消息 547,级别 16,状态 0,第 2 行
UPDATE 语句与 REFERENCE 约束"FK__tb1__addr__3EA749C6"冲突。该冲突发生于数据库"test",表"dbo.tb1", column 'addr'。
语句已终止。*/
--解决方法:
exec sp_helpconstraint tb1
--找到你的约束名
/*
FK__tb1__addr__64CCF2AE
*/
alter table tb1 drop constraint FK__tb1__addr__64CCF2AE UPDATE tB2
SET id = 2008
WHERE (info= 'value')
select * from tb1
/*
1 行受影响)
id name addr
----------- -------------------- -----------
1 a 2008
2 b 2*/