asked on

sql server triggers

actually i am making a pharmacy maintenance project using sql server and i want to fire a trigger from dbo.SaleInfo to dbo.stockinfo using the foreign key in both tables MedID and when stock is decreamented it reduce the stock from dbo.stockinfo?

ASKER CERTIFIED SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

@Chris

we cannot use delete trigger, Line 12...
we only need update or maybe insert...

the inserted table will be null on delete trigger...

maybe something like this

CREATE TRIGGER dbo.SaleInfo_Update_StockAmount 
   ON  dbo.SaleInfo 
   AFTER UPDATE
AS 
BEGIN
  With TotalChanges as (
   SELECT MedID, d.QTY oldQty, i.QTY newQty
     FROM INSERTED I 
    inner join DELETED d on d.MedID=i.MedID
  )
  UPDATE SI
     SET Qty = Qty + (newQty - oldQty)
    FROM dbo.stockinfo SI
   INNER JOIN TotalChanges TC on tc.MedID=d.MedID;
END;

Open in new window

SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HainKurt

@Chris

the question was

when stock is decreamented it reduce the stock from dbo.stockinfo

so, we only need an update trigger here...

your trigger will run in all delete/insert/update statements
your trigger will increase by qty on insert, and decrease by qty on delete row as well...

Chris Luttrell

Hi talha m,
Please select a solution that works for you.
Thanks,
Chris

Chris Luttrell

Answered question and provided test code for validation.