SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Problem in UPDATE AFTER trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rockstar283
Yak Posting Veteran

90 Posts

Posted - 11/23/2010 :  05:05:16  Show Profile  Reply with Quote
following is the trigger I wrote:

USE "E:\STUDY\LAST SEM\PROJECT\FINANCE1\APP_DATA\DB\FINANCE.MDF";
GO
IF OBJECT_ID ('dbo.investment_update','TR') IS NOT NULL
DROP TRIGGER investment_update;
GO
CREATE TRIGGER dbo.investment_update
ON investment
AFTER UPDATE
AS
DECLARE @N float,@T_C float,@C_T float,@C_R float,@D float,@C float;

SELECT @N=num,@T_C=total,@C_T=Current_total,@C_R=Current_rate FROM investment

SET @D=@C_T-@T_C

if @D>0
SET @C = (@D*30)/100
else
SET @C = 0

UPDATE investment SET Difference=@D,Commission=@C
GO

Basically, I have 2 columns..current_cost which gives the today's cost of the share and total_cost as the cost at time when the investment was made..the total_cost will ramain the same but current_cost will change daily according to today;s share market..so using the trigger given above I am trying to calculate the commission earned if the customer makes some profit by selling the shares today..
but it is not updating the table properly..any suggestions abt thhis trigger?

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/23/2010 :  05:23:14  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
>> SELECT @N=num,@T_C=total,@C_T=Current_total,@C_R=Current_rate FROM investment
>> UPDATE investment SET Difference=@D,Commission=@C

Is there only one row in the investmenttable?
maybe you want
SELECT @N=num,@T_C=total,@C_T=Current_total,@C_R=Current_rate FROM inserted

UPDATE investment SET Difference=@D,Commission=@C
where investment.pk = inserted.pk


That's if you are only dealing with single row updates

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockstar283
Yak Posting Veteran

90 Posts

Posted - 11/23/2010 :  11:10:28  Show Profile  Reply with Quote
no i have many rows in the table..still the code you gave will work? or what shall be done for multiple rows..
also even though there are multiple rows in the table..I am updating the table row by row from code behind..
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/23/2010 :  11:19:04  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Yes the coed I gave should work.
the deleted and inserted tables hold the before and after images of the rows updated so you can join to these to update the rows affected.
You don't actually need the variables - you could do the update in a single statement and then that woud work for multiple row updates.
If you don't cater for this then you should throw an error if more than one row is updated.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockstar283
Yak Posting Veteran

90 Posts

Posted - 11/24/2010 :  00:03:08  Show Profile  Reply with Quote
UPDATE investment SET Difference=@D,Commission=@C where investment.investment_id = inserted.investment_id


got an error :


Msg 4104, Level 16, State 1, Procedure investment_update, Line 18
The multi-part identifier "inserted.pk" could not be bound.

:(

Edited by - rockstar283 on 11/24/2010 00:15:31
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/24/2010 :  04:01:23  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
UPDATE investment
SET Difference=@D,Commission=@C
from investment
join inserted
on investment.investment_id = inserted.investment_id

you could also do this without using variables
UPDATE investment
SET Difference = inv.Current_total - inv.total,
Commission = case when inv.Current_total - inv.total > 0 then ((inv.Current_total - inv.total)*30)/100 else 0 end
from investment inv
join inserted i
on inv.investment_id = i.investment_id


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

rockstar283
Yak Posting Veteran

90 Posts

Posted - 11/24/2010 :  04:17:08  Show Profile  Reply with Quote
tht has worked in my case.. :)





there is only one problem now..if c_t and t_c remains same then the difference is turning out be something other than 0..hw is tht possible? check out the image if possible
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 11/24/2010 :  06:04:08  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I'm guessing these are floats which are not precise values. Can always get a slight error.
try subtracting the columns and see what you get.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000