if object_id('testtable2') is not null
drop table testtable2
if object_id('testtable1') is not null
drop table testtable1
GO
----创建主键表,主键列为pid,IDENTITY列为id,并为id设置UNIQUE约束,保证id唯一
CREATE TABLE testTable1(id int IDENTITY(1,1) NOT NULL ,pid varchar(10) NOT NULL) ON [PRIMARY]
GO
ALTER TABLE testTable1 ADD
CONSTRAINT PK_testTable1 PRIMARY KEY CLUSTERED (pid) ON [PRIMARY] ,
CONSTRAINT IX_testTable1 UNIQUE NONCLUSTERED (id) ON [PRIMARY]
GO
----创建外键表,ChildID为级联更新外键,fidx和fidy为非级联外键
CREATE TABLE testTable2(ChildID varchar(10),fidx varchar(10),fidy varchar(10)) ON [PRIMARY]
GO
ALTER TABLE testTable2 ADD
CONSTRAINT FK_testTable2_testTable1 FOREIGN KEY (ChildID) REFERENCES testTable1(pid) ON DELETE CASCADE ON UPDATE CASCADE NOT FOR REPLICATION ,
CONSTRAINT FK_testTable2_testTable11 FOREIGN KEY (fidx) REFERENCES testTable1(pid) NOT FOR REPLICATION ,
CONSTRAINT FK_testTable2_testTable12 FOREIGN KEY (fidy) REFERENCES testTable1(pid) NOT FOR REPLICATION
----禁止非级联外键的'强制INSERT和UPDATE约束'
alter table testTable2 nocheck constraint FK_testTable2_testTable11
alter table testTable2 nocheck constraint FK_testTable2_testTable12
GO
----插入测试数据
insert into testtable1(pid) values('1')
insert into testtable1(pid) values('2')
insert into testtable1(pid) values('3')
insert into testtable1(pid) values('4')
insert into testtable1(pid) values('5')
insert into testtable1(pid) values('6')
insert into testtable2(ChildID,fidx,fidy) values('1','6','6')
insert into testtable2(ChildID,fidx,fidy) values('2','5','5')
insert into testtable2(ChildID,fidx,fidy) values('3','4','4')
insert into testtable2(ChildID,fidx,fidy) values('4','3','3')
insert into testtable2(ChildID,fidx,fidy) values('5','2','2')
insert into testtable2(ChildID,fidx,fidy) values('5','1','1')
GO
----创建UPDATE触发器
CREATE TRIGGER tri_update_tab1 on testtable1
for update
as
if update(pid)
begin
update a set fidx = i.pid from testtable2 a,inserted i,deleted d where a.fidx = d.pid and i.id = d.id
update a set fidy = i.pid from testtable2 a,inserted i,deleted d where a.fidy = d.pid and i.id = d.id
/*----如果楼主的表中没有id列,请使用以下代码,使inserted表和deleted表能行对应
if object_id('tempdb..#inserted') is not null
drop table #inserted
if object_id('tempdb..#deleted') is not null
drop table #deleted
select ids = identity(int,1,1),* into #inserted from inserted
select ids = identity(int,1,1),* into #deleted from deleted
update a set x = i.pid from table2 a,#inserted i,#deleted d where a.x = d.pid and i.ids = d.ids
update a set y = i.pid from table2 a,#inserted i,#deleted d where a.y = d.pid and i.ids = d.ids
drop table #inserted,#deleted
*/
end
GO
----创建DELETE触发器,将外键值设为NULL
CREATE TRIGGER tri_delete_tab1 on testtable1
for delete
as
update a set fidx = NULL from testtable2 a,deleted d where a.fidx = d.pid
update a set fidy = NULL from testtable2 a,deleted d where a.fidy = d.pid
GO
select * from testTable1
select * from testTable2
update testTable1 set pid=999 where id=5;