Create Delete trigger that has Foreign key constraints on the table

Question

Post reply

Create Delete trigger that has Foreign key constraints on the table

pcq0125

SSCrazy

Points: 2890
More actions
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

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

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

SqlDeveloper2246 SSC Eights! Points: 886 More actions · Answer 1

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.

pcq0125 SSCrazy Points: 2890 More actions · Answer 2

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.

SqlDeveloper2246 SSC Eights! Points: 886 More actions · Answer 3

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).

pcq0125 SSCrazy Points: 2890 More actions · Answer 4

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.

SqlDeveloper2246 SSC Eights! Points: 886 More actions · Answer 5

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.