关于外键约束。

[愉快的登山者]

有两个表A和B，表B中的一个数据项与表A的主键外键关联。
当用表A的主键做为条件删除表A的某一记录时，如果表B中有关联的记录，则显示：
DELETE statement conflicted with COLUMN REFERENCE constraint 'fk_ts_log_operatorid'. The conflict occurred in database 'SHIPPING', table 'B', column 'operatorid'.
The statement has been terminated.
增加触发器也不管用：
CREATE TRIGGER del_A on A for delete
AS
delete B from deleted where B.operatorid = deleted.operatorID
仍然出现上面提示。
我如何才能实现，再删除A时，对应的B记录也能删除。

[lyyrw]

把外键去掉，用触发器关联——我就是这样做的。

[leimin]

增加关联更新和删除的约束.LIKE BELOW
(THIS FUNCTION ONLY ADAPT TO SQLSERVER2K,NOT ADAPT TO SQLSERVER7.0)
ALTER TABLE dbo.TABLE_A ADD
CONSTRAINT FK_TABLE_B FOREIGN KEY
(COLUMN_A) REFERENCES dbo.TABLE_B (COLUMN_) ON DELETE CASCADE ON UPDATE CASCADE

[j9988]

要么删除外键约束，光用触发器
alter table B drop constraint fk_ts_log_operatorid

要么先删除再建级联删除
alter table B drop constraint fk_ts_log_operatorid
ALTER TABLE B ADD CONSTRAINT FK_A_B FOREIGN KEY (operatorid) REFERENCES A(operatorid) ON DELETE CASCADE

[CSDNM]

1、级联删除，leimin(黄山光明顶) 的方法和 j9988(j9988)的方法2。
2、删除外键约束，光用触发器，j9988(j9988) 的方法1。
3、用INSTEAD OF DELETE 触发器:

CREATE TRIGGER del_A on A
INSTEAD OF DELETE
AS
delete B from deleted where B.operatorid = deleted.operatorID
delete a from deleted where a.keyfield=deleted.keyfield
go

[CSDNM]

4、用存储过程（没有写全，应该还有出错事务回滚处理）：
begin tran
delete B from a where B.operatorid = a.operatorID and a的条件
delete a where a的条件
commit tran

[pchddcat]

用存储过程和视图
就这么简单

[ToUpdate]

都解决了！！
哈哈！

[奇遇]

:)

[ravenkatte]

哦~~

[microlong]

删除两个外键不就可以了嘛
DROP FOREIGN KEY ……

[CSDNM]

级联删除和INSTEAD OF DELETE 在7。0中不能使用。

[愉快的登山者]

感谢各位大力帮助，去掉外键是一种方法。
CSDNM(CSDN经理(信就不假) :
INSTEAD OF DELETE 在7。0中好象不能使用。