how to use triggers to performe foreign key constrain based on multiple tables
Posted on 2007-12-04
I am writing three triggers two mimic a Primary and Foreign key constraint between three tables and its parent table. What I mean and wan to do is the following:
I have a table called Items. Items contain a primary key on itemID field. It contains other fields such as itemTypeID, ie memory, cpu, motherboard & Also, it contains other fields such as name, series, model, brand, imageFileName, Price &.
I would like to make Items table a generic table for other tables that contain more specific item details.
Now, assume that I have the following tables for which Item works as a generic table: memoryDetail, cpuDetail, motherBoardDetail&
The specifications are as follows when trying to insert a record to the parent and child table:
* ItemID in Items table must be a primary key
* ItemID must serve as a Foreign key constrain in memoryDetail, cpuDetail,
* ItemID must exist only once in one of the derived(detail) tables. What I mean is that
memoryDetail and cpuDetail can not have the same ItemID. The same condition holds
true for any conbination of the derived tables. Derived tables cannot share the same
* ItemID cannot be duplicated in any of the derived (detail) tables.
* If ItemID exist in one of the details table and an attempt to reuse the same itemID in another detail table occurs, an
error message (RAISERROR Method) should come up detailing the two tables that cost the error to be raised.
I have been trying to use a For Insert trigger to performer the above constrain. However, I cannot get the joins to work properly. Please, can some one show me how to do this query (trigger) for one of the details table and I would replicate the query and adjust it accordantly to work for the other details tables.