Trigger to check a foreign key without using foreign key 
Author Message
 Trigger to check a foreign key without using foreign key


I want to use a trigger to check a foreign key.

Let's assume :

table LIGNES_V contains field ID_ARTICLE char(25)
table STOCK contains field S_ID char(10)

In table LIGNES_V, ID_ARTICLE must contain '' or a valid value from S_ID in
(because of the '' -> could not use a standard foreign key)

In the update trigger of LIGNES_V, i placed

if update(id_article) and exists (select id_article from inserted where
(id_article>' ') and  (left(id_article,10) not in (select s_id from stock)))
 RAISERROR ( 'ID_ARTICLE does not match S_ID !',16,1 )

it works very fine and gives the correct result.

BUT : i assume that this request containing a sub-request will result in bad

Does somebody have an idea to write this trigger on a more-performing way ?

Thanks for help,

Kind regards,


Fri, 15 Oct 2004 04:50:15 GMT
 [ 1 post ] 

 Relevant Pages 

1. Trigger to check a foreign key without using foreign key

2. foreign key w/o foreign/primary key

3. Foreign Key as subset of Primary Key

4. Identification of a primary key/foreign key

5. One FOREIGN KEY refrences a Table with two Primary Keys

6. Copying Primary Key to Foreign Key

7. Changing Primary Key Values when foreign key constraints exist

8. delete primary key records together with the foreign keys records

9. Help, foreign keys and index keys

10. Replication with Primary Keys & Foreign Keys Schema

11. Foreign Key and Primary Key Programatic retrieval

12. Update primary key & Foreign key

Powered by phpBB® Forum Software