Problem in UPDATE AFTER trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem in UPDATE AFTER trigger

Author

Topic

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2010-11-23 : 05:05:16

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
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 05:23:14

>> 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.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2010-11-23 : 11:10:28

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..

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 11:19:04

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.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2010-11-24 : 00:03:08

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.

:(

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 04:01:23

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.

rockstar283
Yak Posting Veteran

96 Posts

Posted - 2010-11-24 : 04:17:08

tht has worked in my case.. :)

http://img831.imageshack.us/img831/4112/89736298.png "/>

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-24 : 06:04:08

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources