2

I have two tables, let's call them A and B. They have One to many relationship.

Table A

Trxid int,
TotalAmount decimal(10,2)

Table B

Trxid int,
TblBcol Varchar,
Amount decimal(10,2)

Table B contains breakup details for Table A.

My requirement : sum of Amount column of Table B should be equal to Table A's TotalAmount column for each trxid.

Is there any constraint available to achieve this or is there any other way?

TT.
  • 15,774
  • 6
  • 47
  • 88
user7199624
  • 21
  • 2
  • 3
    One option is to replace Table A with a view. – David Rushton Nov 23 '16 at 11:26
  • Other option may be create a trigger on `TableB` which inserts or update respective row in `TableA`. but the idea suggested by @destination-data is more appropriate. – Ubercool Nov 23 '16 at 11:31
  • @destination-data Thanks for response. Table A contains other columns also. Moving them to Table B will result redundancy. – user7199624 Nov 23 '16 at 11:47
  • @Pramod thanks. but every row insert of Table B is checked against total amount and results validation fails. – user7199624 Nov 23 '16 at 11:50
  • It is a bit unclear to me - is A.TotalAmount filled separately or must it always be sum of B.Amount? If it is filled separately and is used only to check document validity, then you should not fail check while inserting rows into B - otherwise you just cannot insert anything, unless it contains full amount. You have to validate entire document afterwards, not SQL task (but can use SQL procedure). – Arvo Nov 23 '16 at 11:55
  • Alternative - create computed column with function, see example [here](http://stackoverflow.com/questions/13488822/create-computed-column-using-data-from-another-table#13488881) – Konstantin Taranov Nov 23 '16 at 12:17
  • @KonstantinTaranov: I am not sure that a computed function would work here. IIRC you can only use UDFs in a computed column if they are deterministic (i.e. if they always return the same value for the same input values). Since the function would have to query another table, SQL Server is likely not going to categorize it as a deterministic function, and therefore it couldn't be used in the computed column specification. (But to be honest, this is from memory; I haven't actually tried.) – stakx - no longer contributing Nov 23 '16 at 12:33

1 Answers1

1

There are a few possible solutions for this. Choose according to your preferences.

  1. Get rid of the redundancy. Having amount columns in both tables that have to match is probably not necessary. It might be enough to have only one amount column, so there would never be a need in the first place to keep them "in sync". You have a foreign key relationship between the two tables, so your database clients can always JOIN them to "attach" the amount column from one table to the other for the purpose of querying.

  2. Remove Amount from B and create a view. If your client requires a table containing the same information that is in your current B table, but they only query that table, you could replace it with a view:

    CREATE VIEW B WITH SCHEMABINDING AS
SELECT BWithoutAmount.Trxid, BWithoutAmount.TblBcol, A.TotalAmount AS Amount
FROM BWithoutAmount JOIN A ON BWithoutAmount.Trxid = A.Trxid;

    This assumes that there is a base table BWithoutAmount that replaces your current B, but doesn't have the Amount column. The B view takes the place of your current B base table.

  3. Define a TRIGGER on tables A and B. Their task would be to update the Amount column in B so it matches the amount from the corresponding row in A. The triggers need to run in the following cases:

    • When a row in A is updated.
    • When a row in B is inserted.

    The FK constraint and ON DELETE CASCADE should take care of the rest.

stakx - no longer contributing
  • 83,039
  • 20
  • 168
  • 268