Very Computer

Two Questions:
1. Why doesn't the following trigger work?  Note that STUDENTS is the
master table and STUDCOUNSELORS and TESTS are detail tables.  All three
tables have a field called FSID.  In this case, there are NOT foreign keys
in the detail tables. When the STUDENT record is deleted, the detail
records from the detail tables are NOT deleted.

CREATE TRIGGER CASCADE_DELETES ON dbo.STUDENTS
FOR DELETE
AS
begin

  from deleted d

  delete from STUDCOUNSELORS

  delete from TESTS

end

2. What is the best way to implement a cascading delete in a Master-Detail
relationship when a foreign key DOES exist in the detail tables?  Trigger
or Stored Proc?

David Sweeney
Texas A&M University

David,

If I understand the question--the trigger will only work if you are not
using declarative RI in the detail tables. Declarative RI is restrictive
and in conflict with the trigger operation. Are you using declaratvie RI
in the tables?

Matt

   DELETE tests
   FROM   tests t, deleted d
   WHERE  t.fsid = d.fsid

The above will only work properly if you delete one record at a time,
and a trigger must be able to handle multi-row deletes (as well as
multi-row updates and inserts).

Quote:>2. What is the best way to implement a cascading delete in a Master-Detail
>relationship when a foreign key DOES exist in the detail tables?  Trigger
>or Stored Proc?

--

F=F6r =F6vrigt anser jag att QP b=F6r f=F6rst=F6ras.
B=65sid=65s, I think QP should b=65 d=65stroy=65d.