Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

when one column is updated, want to update another column in same table using trigger. Expand / Collapse
Author
Message
Posted Wednesday, September 24, 2008 11:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:01 AM
Points: 1,557, Visits: 2,613
Dear All,

I have a table in which there are fields like Quantity, Price, Total etc.There's is also a primary key field in this table.
We have an interface where user can change the quantity (only quantity).

I want to write a trigger on this table which calculates Total (qty * price) whenever qantity is updated.

How can I do it..?. How will I know for which row the quantity is updated..?

Pleas help...




Thanks in advance..
Santhosh Nair.



Post #575707
Posted Thursday, September 25, 2008 12:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 4:11 AM
Points: 2,397, Visits: 3,416
Something similar to this?
CREATE TRIGGER dbo.trgMyTotalUpdate ON dbo.MyTable
AFTER UPDATE
AS

IF UPDATE(Quantity)
UPDATE mt
SET mt.Total = i.Quantity * i.Price
FROM dbo.MyTable AS mt
INNER JOIN inserted AS i ON i.PkCol = mt.PkCol




N 56°04'39.16"
E 12°55'05.25"
Post #575737
Posted Thursday, September 25, 2008 1:24 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 4:01 AM
Points: 1,557, Visits: 2,613

Thanks a lot...It's working...
Post #575751
Posted Thursday, September 25, 2008 10:31 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:55 AM
Points: 774, Visits: 1,195
how about a Computed Column that "computes" i.Quantity * i.Price

http://www.sqlservercentral.com/articles/User-Defined+functions/complexcomputedcolumns/2397/


SQLServerNewbie

MCITP: Database Administrator SQL Server 2005
Post #576189
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse