birnbaum@paul.rutgers.edu (Rich Birnbaum) (06/09/90)
I'm writing triggers for update and delete that disallow updates
to the primary key and/or deletion of a record IFF there are dependent
records in other tables, i.e. a table with the key as a foriegn key
containing the values being deleted.
e.g
table1 table2
------ ------
id1 int /*id1 + id2 are the */ otherid int
id2 int /*primary key for table 1*/ ....
.... id1 int /* id1 + id2 are a foreign key */
id2 int /* to table 1 */
I want to disallow deletion of a record from table1 if there is a record
in table2 with matching id1+id2. It seems simple enough - attached the
following trigger for delete to table1:
if exists (select * from deleted,table2 where
deleted.id1=table2.id1 and
deleted.id2=table2.id2)
begin
rollback tran, etc.
end
The problem is that, even though I have an index (not unique or clustered)
on table2 (id1,id2), it is not used and since table2 is quite big, the
trigger takes forever.
QUESTION:
1) Why isn't the index used in the above trigger's join?
2) Is there a better way (or at least another way) to do this?
I have tried all sorts of things - each of which came to a dead end.
I would glad to tell my entire tale of woe to an interested party.