| Author |
 Topic  |
|
|
rockstar283
Yak Posting Veteran
73 Posts |
 Posted - 11/23/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/23/2010 : 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
73 Posts |
Posted - 11/23/2010 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/23/2010 : 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
73 Posts |
Posted - 11/24/2010 : 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.
:( |
Edited by - rockstar283 on 11/24/2010 00:15:31 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/24/2010 : 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
73 Posts |
Posted - 11/24/2010 : 04:17:08
|
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 |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 11/24/2010 : 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. |
|
|
| |
Topic |
|
Problem in UPDATE AFTER trigger
