How can i add in a table and substract the same amount from another?-CodePudding

I have 2 tabeles: stock with:

productID
quantity
limit

and buyer with:

name
surname
address
desiredquantity
productID

How can i set it so that when i add to desiredquantity from buyer it subtracts the same amount from quantity in the stock table?

CodePudding user response：

Ideally you should add a check constraint on the quantity field on stock table so that it doesn't allow a value less than 0.

Then you can write a trigger on the buyer table which on insert into the buyer table would subtract the same quantity on the stock table.

Instead for such cases it is desired to use a ledger table which has every transaction of the stock coming in and going out and gets a record inserted into it with the type of transaction , ' ' if inward '-' if outward and the stock can be a calculated field.

CodePudding user response：

Add the check constraint by replacing the TABLENAME with actual table name

ALTER TABLE <TABLENAME>
ADD CONSTRAINT CHK_<TABLENAME> CHECK (Quantity>0 )

-- FOR CREATING THE TRIGGER, THIS SHOULD DRIVE THE CONCEPT, PLEASE MAKE APPROPRIATE CHANGES TO SUIT YOU REQUIREMENT.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[tr_<TableName2>_Insert]
   ON [dbo].[item_detail]
   AFTER INSERT
AS BEGIN
    SET NOCOUNT ON;

        UPDATE <Table1>
        SET quantity = ISNULL(quantity,0) - ISNULL(inserted.desiredquantity,0)
        FROM
            inserted 
        WHERE 
            inserted.productid = <table1>.productid

END