none
求助:触发器级联修改、删除 RRS feed

  • 问题

  • 有两个表,删除、修改一张表的时候,另一张表的也随着改变。

    代码如下啊:

    create table cat_tb
    (
      cat_id int primary key,
      cat_name varchar(20)
    )
    insert into cat_tb values(2001,'电器')
    insert into cat_tb values(2002,'汽配')


    create table sub_tb
    (
     sub_id int  primary key,
     sub_name varchar(20),
     cat_id int foreign key(cat_id) references cat_tb
    )

    insert into sub_tb values(1,'鼠标',2001)
    insert into sub_tb values(2,'键盘',2001)
    insert into sub_tb values(3,'零件',2002)

     

    举例说明:我想修改cat_tb表的cat_id=2001修改为2010,此时sub_tb表cat_id就变为2010

     

    现在用触发器或者事务怎么去实现这个功能?多谢

     


    you are welcome
    2011年3月20日 14:32

答案

  • create table cat_tb ( cat_id int primary key, cat_name varchar(20) )

    go insert into cat_tb values(100,'数码电子')

    insert into cat_tb values(200,'化妆品')

    insert into cat_tb values(300,'家具')

    go

    create table sub_tb ( sub_id int primary key, sub_name varchar(20), cat_id int foreign key(cat_id) references cat_tb )

    go

    insert into sub_tb values(1000,'电脑',100)

    insert into sub_tb values(2000,'鼠标',100)

    insert into sub_tb values(3000,'美容品',200)

    insert into sub_tb values(4000,'桌子',300)

    insert into sub_tb values(5000,'凳子',300)

    go

    CREATE trigger cat_sub

    on cat_tb

    INSTEAD OF delete,update

    as if exists(select 1 from inserted) and exists(select 1 from deleted)

    begin update sub_tb set cat_id=null where cat_id=(select TOP 1 cat_id from deleted)

    UPDATE cat_tb SET cat_id=(select TOP 1 cat_id from inserted) where cat_id=(select TOP 1 cat_id from deleted)

    update sub_tb set cat_id=(select TOP 1 cat_id from inserted) where cat_id IS NULL print '修改成功 ' end

    else if exists(select 1 from deleted)

    begin delete sub_tb where EXISTS(select 1 from DELETED WHERE cat_id=sub_tb.cat_id) DELETE cat_tb WHERE EXISTS(SELECT 1 FROM DELETED WHERE cat_id=cat_tb.cat_id)

    print '删除成功' end GO update cat_tb set cat_id=400 where cat_id=300 go SELECT * FROM sub_tb--查看結果 /* SELECT * FROM sub_tb */ DROP TABLE sub_tb,cat_tb


    you are welcome
    2011年3月22日 9:10

全部回复

  • References ... Cascade update and delete,查下帮助,答案就出来了
    Try SQL Server 2008 QQ:315054403 dgdba@hotmail.com
    2011年3月21日 1:06
  • 用触发器或者事务怎么去实现这个功能


    you are welcome
    2011年3月21日 5:51
  • I see.  But I don't kown what writed

    you are welcome
    2011年3月21日 16:32
  • You may need formal t-sql training.
    2011年3月21日 17:44
  • create table cat_tb ( cat_id int primary key, cat_name varchar(20) )

    go insert into cat_tb values(100,'数码电子')

    insert into cat_tb values(200,'化妆品')

    insert into cat_tb values(300,'家具')

    go

    create table sub_tb ( sub_id int primary key, sub_name varchar(20), cat_id int foreign key(cat_id) references cat_tb )

    go

    insert into sub_tb values(1000,'电脑',100)

    insert into sub_tb values(2000,'鼠标',100)

    insert into sub_tb values(3000,'美容品',200)

    insert into sub_tb values(4000,'桌子',300)

    insert into sub_tb values(5000,'凳子',300)

    go

    CREATE trigger cat_sub

    on cat_tb

    INSTEAD OF delete,update

    as if exists(select 1 from inserted) and exists(select 1 from deleted)

    begin update sub_tb set cat_id=null where cat_id=(select TOP 1 cat_id from deleted)

    UPDATE cat_tb SET cat_id=(select TOP 1 cat_id from inserted) where cat_id=(select TOP 1 cat_id from deleted)

    update sub_tb set cat_id=(select TOP 1 cat_id from inserted) where cat_id IS NULL print '修改成功 ' end

    else if exists(select 1 from deleted)

    begin delete sub_tb where EXISTS(select 1 from DELETED WHERE cat_id=sub_tb.cat_id) DELETE cat_tb WHERE EXISTS(SELECT 1 FROM DELETED WHERE cat_id=cat_tb.cat_id)

    print '删除成功' end GO update cat_tb set cat_id=400 where cat_id=300 go SELECT * FROM sub_tb--查看結果 /* SELECT * FROM sub_tb */ DROP TABLE sub_tb,cat_tb


    you are welcome
    2011年3月22日 9:10