Path: utzoo!attcan!uunet!cs.utexas.edu!swrinde!zaphod.mps.ohio-state.edu!brutus.cs.uiuc.edu!psuvax1!rutgers!aramis.rutgers.edu!paul.rutgers.edu!birnbaum From: birnbaum@paul.rutgers.edu (Rich Birnbaum) Newsgroups: comp.databases Subject: Sybase trigger/index help, please Keywords: primary/foreign key, Sybase 4.0 Message-ID: Date: 8 Jun 90 21:09:32 GMT Organization: Rutgers Univ., New Brunswick, N.J. Lines: 39 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.