CODEWITHSUNDEEP
sqlsql-server
Dragoi Eduard
Dragoi Eduard

Reputation: 3

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

I have 2 tabeles: stock with:

and buyer with:

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?

Upvotes: 0

Views: 58

Answers (2)

Prasad Bhogadi
Prasad Bhogadi

Reputation: 32

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

Upvotes: 0

Prasad Bhogadi
Prasad Bhogadi

Reputation: 32

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.

Upvotes: 1

Related Questions