Posted by Erland Sommarskog on 10/01/57 11:41

(heyvinay@gmail.com) writes:
> I have transaction table where the rows entered into the transaction
> can come a result of changes that take place if four different tables.
> So the situation is as follows:
>
> Transaction Table
> -TranId
> -Calc Amount
>
> Table 1 (the amount is inserted into the transaction table)
> - Tb1Id
> - Tb1Amt
>
> Table 2 (an amount is calculated based on the percentage and inserted
> into the transaction table)
> -Tbl2Id
> -Tb2Percentage
>
> Table 3 (the amount is inserted into the transaction table)
> -Tbl3Id
> -Tbl3Amut
>
> Table 4 (an amount is calculated based on the percentage and inserted
> into the transaction table. )
> -Tbl2Id
> -Tb2Percentage
>
> How do I create referential integrity between the Transaction table and
> the rest of the tables. When I make changes to the values in Table 1 -
> 4, I need to be able to reflect this in the Transaction table.

Depends on what you mean with changes, but obviously if you change an
amount in one table and you want that to affect the CalcAmount in the
Transaction table, then you should consider a trigger. For that to
work, the Transaction table need to have one FK column per referencing
table.


Your description was quite brief, and very abstract. It is not at all
impossible that there is a better design, if you can give more meat of
what is behind the various tables.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]