Post reply

Create Delete trigger that has Foreign key constraints on the table

  • June 23, 2008 at 11:27 am

    #208738

    Hi,

    I need to create a delete trigger on the table but there is a foreign key contraint on the table. Prior to deletion I need to store the deleted data on a storage table. Anyone knows how can I go about doing it? I have expample below. Thanks.

    Salesorder table

    ----------------

    sokey sonum description

    FK constraint sokey --sodetkey

    Salesordetail

    ------------

    sodetkey lineitem shipadress sokey

    FK constraint sodetkey --solinekey

    Salesorderline

    --------------

    solinekey linedescription sodetkey

    and I need to store these values from these 3 tables into 1 table below

    DeletedValue Tbl

    -----------------

    sokey sodetkey solinekey sonum lineitem shipadress linedescription

  • June 23, 2008 at 11:55 am

    #832952

    you can make usage of DELETED table, which you can access inside the DELETE trigger.

    it's just a JOIN statement

    I think you want the trigger to be on the third table, so the needed statement should be:

    INSERT INTO DeletedValuesTable

    SELECT SO.sokey, SOD.sodetkey, SOL.solinekey, SO.sonum, SOD.lineitem, SOD.shipadress, SOL.linedescription

    FROM SalesOrder SO, SalesOrderDetails SOD, DELETED SOL

    WHERE SO.sokey = SOD.sodetkey

    AND SOD.sodetkey = SOL.sodetkey

    this is supposed to work successfully.

    The whole point is to join the first two tables with the DELETED table instead of the third table.

    I hope this is what you need.

  • June 23, 2008 at 1:22 pm

    #832992

    Hi Grasshoper,

    Thanks for your help. You have answered of my question. I apologize I did not not make myself clear. I wanted to know where should I place my trigger if I want to delete the entire Salesorder. Should I place the trigger on the Salesorder or Salesordetail, or Saleorderline since there are Foreign key constraint? Please advise. Thanks.

  • June 23, 2008 at 1:45 pm

    #833002

    I think you're making an auditing system for your database.

    if you put the trigger on the first table, you have to worry about two points:

    - Generally speaking, and according to MSF, you should not allow delete on the parent table unless it has no childs in the child tables. so if you want to accomodate Microsoft's methodology, you should check for the child records before you delete the parent record, in case they exist, you should not allow this delete operation.

    - anyway, if you want your constraint to cascade the delete operations and delete the child nodes automatically, then you have to put the trigger on the first table, but what if some delete operations take place in the child tables?

    The Trigger won't fire because it's related only to the parent table, so in your case it's not practical.

    So in my openion, i think it's better to use the trigger in the child tables (the third one in your case).

  • June 23, 2008 at 1:58 pm

    #833010

    Hi FerasGer83

    Thanks for your tips. This is what I thought to place the trigger on the last child table. Another question if I delete from the last child table then I should not need to disable the FK constraint since the delete starts from the last child table. Am i right? Thanks.

  • June 23, 2008 at 2:22 pm

    #833015

    Sure, if you delete from the lowest level up to the highest one, you do not need any more to disable any constraint or use the cascade option.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply