sql级联修改删除.docVIP

--sql语句实现级联修改和删除 ?-- 此代码有点危险,,级联删除少用,,不然到时连数据表中记录怎么少了都不知道 ?ALTER?? TABLE? [tablename]?? DROP?? CONSTRAINT? FK_nameALTER TABLE [tablename] add CONSTRAINT [FK_name] FOREIGN KEY (??? [fk_tableid]) REFERENCES [pk_tablename] (??? [pk_tableid] )on update cascade on delete cascade ? 如果，现在有两张表A(id,xx.xx...),C(id,Cid,xx,xx...)，预实现C中的id与A 中id级联删除、级联更新/数据同步，可有两种实现方式： 一. 利用sql server自带的级联删除、级联更新功能，即其外键约束途径 ? ? alter table dbo.C add constraint FK_C_A_AID foreign key(AID) references dbo.A(AID) on delete cascade on?update casade go ? ? alter table dbo.C add constraint FK_C_B_BID foreign key(BID) references dbo.B(BID) on delete cascade on?update casade go ? ? ? ? ? 级联更新和级联删除方式： Alter TABLE [dbo].[T_USERGROUP] ADD ??????? CONSTRAINT [FK_T_USERGROUP_T_ACCTTEMPLATE] FOREIGN KEY ??????? ( ??????????????? [ATNAME] ??????? ) REFERENCES [dbo].[T_ACCTTEMPLATE] ( ??????????????? [ATNAME] ??????? ) ON Update CASCADE ON Delete CASCADE ? ? 二、另外，我们也完全可以自已写一个触发器来实现此功能 ? ???? ???? ???? A: ? ???? int id ?????varchar(20) name ???? ???? C: ???? int id ?????int Cid ?????varchar(100) info ? ???? 假设表A、C的字段如上： ? ???? 如以级联删除表A、C记录为例，实现语句如下： ???? (如果删除表A中的记录，则自动删除表C中与表A中id相同的记录) ? ????CREATE?? TRIGGER trg_a ON A FOR INSERT,UPDATE,DELETE AS BEGIN ????????????? DECLARE @nInsRows?? INT?? --用于插入记录条数统计 ????????????? DECLARE @nCount?? INT???? --用于删除记录条数统计??????????????????????? DECLARE @nCOde?? INT????? --删除的记录? -- 统计插入的的记录数并存放在@nInsRows里面 ?SELECT @nInsRows = COUNT(*) ?FROM INSERTED ? -- 统计删除的记录数并存放在@nCount里面 ?SELECT?@nCount = COUNT(*) ?FROM?DELETED ???????????? IF(@nCount 0) ?????? BEGIN ?????? delete?? C?? from?? deleted??? where? C.id=deleted.id??????????????????? ?????? END ? ? ??????????? IF (@nInsRows 0 AND @nCount = 0)?? ?--如果是插入数据 ?BEGIN ???? insert into C select * from inserted ?END ??????????? IF (@nInsRows 0 AND @nCount 0)?? ?-- 如果是更新数据 ?BEGIN ??? select @nCOde=id from inserted ??? IF EXISTS (select * from C where HYPERLINK mailto:C.id=@nCOde C.id=@nCOde) ???? BEGIN?? ???