Very Computer

I have two problems to address:

1- Here is my situation
I have a table2(child), which has a foreign key on table1(parent). Now, we all now that foreign key ensures referential integrity. meaning, 1) that no record can be inserted in table2 if it does not have a primary key in the table1(parent). and 2) no record in parent table1 can be deleted if it has a matching records in table2(child). and 3)) primary key cannot be updated.
However, in my situation, cascade deletion is a must and to do that, a trigger should be created for the Delete. Can such a trigger coexist with foreign key constraints. The foreign constraints is needed to ensure referential integrity for 'insert' and 'update'. That's why i am keeping it.

The worst case scenario, is that i have to create a trigger for update and insert.

2- one more thing,
i had a situation were i was able to rename a field which is part of my primary key and SQL server did not warn me nor issued an error. Additionaly, the renamed field is used by the trigger (i.e. if update(field) )
When the table was updated, the trigger issued no error. How come?

Kindest Regards,
Khaled Nimer Renno
190 Lees Avenue, apt no. 1910
Ottawa, ON,K1S 5L5, Canada
MOBILE   (613) 220-4874
PHONE    (613) 565-5007

WEB         www.geocities.com/SiliconValley/Code/5521

We have two ears and only one tongue in order that we may
hear more and speak less.
                                                                                            -Diogenes

1) First there is an order of operations concerning RI.  When you update a record, whether you update, insert or delete, your Foreign Key is checked.  If your FK fails then no more operations are caried out.  If the FK check is successful then your trigger is fired.  So cascading deletes are next to imposible with Triggers.  You will need to write a SP, or code in your program to go through and clean up all child records before you delete from the parent table.  It sounds like a pain, but it is really not much harder to write than your trigger.  If you must have it in a trigger, then you will have to drop all of your FKs and make all RI happen in INSERT, UPDATE and DELETE tirggers.

2) Because if you study the system tables at all, you will notice that a FK references a column id not a column name, so when you update a column name the change is caried through to the FK.

Jake Massey
MCSD

  I have two problems to address:

  1- Here is my situation
  I have a table2(child), which has a foreign key on table1(parent). Now, we all now that foreign key ensures referential integrity. meaning, 1) that no record can be inserted in table2 if it does not have a primary key in the table1(parent). and 2) no record in parent table1 can be deleted if it has a matching records in table2(child). and 3)) primary key cannot be updated.
  However, in my situation, cascade deletion is a must and to do that, a trigger should be created for the Delete. Can such a trigger coexist with foreign key constraints. The foreign constraints is needed to ensure referential integrity for 'insert' and 'update'. That's why i am keeping it.

  The worst case scenario, is that i have to create a trigger for update and insert.

  2- one more thing,
  i had a situation were i was able to rename a field which is part of my primary key and SQL server did not warn me nor issued an error. Additionaly, the renamed field is used by the trigger (i.e. if update(field) )
  When the table was updated, the trigger issued no error. How come?

  Kindest Regards,
  Khaled Nimer Renno
  190 Lees Avenue, apt no. 1910
  Ottawa, ON,K1S 5L5, Canada
  MOBILE   (613) 220-4874
  PHONE    (613) 565-5007

  WEB         www.geocities.com/SiliconValley/Code/5521

  We have two ears and only one tongue in order that we may
  hear more and speak less.
                                                                                              -Diogenes