CODEWITHSUNDEEP
sqlsql-servertriggers
SellaKumar
SellaKumar

Reputation: 27

SQL Trigger to add values of Two columns and store it in Third column

i have a table with three columns say pqty,prqty and balqty. what i want to do is, have to add values of pqty and prqty. and then it should be stored in balqty. while inserting or updating this table, each row must be affect. i used this trigger, and it worked sometimes and most of times it wont. i dont know why.

CREATE TRIGGER tsl on stockledger
FOR update
AS declare @pqty int, @prqty int;
select @pqty=i.pqty from inserted i;
select @prqty=i.prqty from inserted i;
update Stockledger set balqty = (@pqty - @prqty) 
PRINT 'AFTER Update trigger fired.'

Upvotes: 0

Views: 1582

Answers (1)

Jaaz Cole
Jaaz Cole

Reputation: 3180

I don't think this is a good use of a trigger. Instead, if you have the capacity, consider using a computed column (with PERSISTED to enhance performance):

ALTER TABLE StockLedger DROP COLUMN balqty;
ALTER TABLE StockLedger ADD COLUMN balqty AS pqty - prqty PERSISTED;

Upvotes: 4

Related Questions