SUmt At Every Insert,,,,,,, - SQL Server Forums

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

SUmt At Every Insert,,,,,,,

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 08:20:33

Hi Wid Another Problem.... i want to update a field at the time of insertion mean when i insert the amount column in my table then the column Balance get updated by default......

like dis........

insert into tbl_payment
(
dealer_id,
ref_id,
ref_per,
psub,
pdesc,
amount,
balance
)
values(
'488963',
'2',
'10',
'Comp',
'complete',
'-295',
sum(amount)
)

I dunn Knw whether it is the right way or not but temme the way how can i acheive this??

shaihan
Starting Member

20 Posts

Posted - 2009-02-14 : 09:43:10

INSERT INTO tbl_payment
(dealer_id,ref_id,ref_per,psub,pdesc,amount,balance)
SELECT
'488963','2','10','Comp','complete',-295,sum(amount) + (-295)
FROM tbl_payment
WHERE dealer_id = '488963'

S ]-[ /-\ | ]-[ /-\ N

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 10:16:47

but it gives me wrong thing in my solution....
mean frst i have a row in my table in which amount is 295 noe we enter amount amount is -295 so this time balance must be 0(zero).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 10:52:07

nope. for that you need to do the updating part inside a trigger, because only after commiting the current insertion the record with negative amount will be available.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 11:18:30

ohkk thanks for that vishak sir.....

can u tell me how can i write that trigger .....so that my value get updated..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 11:35:11

something like


CREATE TRIGGER YourTriggerName
ON YourTable
AFTER INSERT 
AS 
BEGIN
UPDATE t
SET t.balance=(SELECT SUM(Amount) FROM Table)
FROM Table t
INNER JOIN INSERTED i
ON i.PK=t.PK
END

PK is primary key of your table.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 11:57:42

ohkk thanxxxxxxxxx man
jus waitin for ur reply,,,its rilli important for me,,,,,,,,,,
Thanks Again?

shaihan
Starting Member

20 Posts

Posted - 2009-02-14 : 12:06:22

sorry. i mistyped. i have updated my last post. it will work without a trigger.

S ]-[ /-\ | ]-[ /-\ N

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 12:10:26

duun knw buut a error.........

Object 'dbo.tbl_payment' does not exist or is invalid for this operation.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 12:12:18

does the table exist?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 12:17:51

yes
table exist.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 12:18:43

and do you have insert permissions in it?

shaihan
Starting Member

20 Posts

Posted - 2009-02-14 : 12:19:14

add the following before the statement

use YOURDATABASENAME
go

S ]-[ /-\ | ]-[ /-\ N

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 12:19:23

wai i try i make u see......

CREATE TRIGGER Amount_update
ON dbo.tbl_payment
AFTER INSERT
AS
BEGIN
UPDATE t
SET t.balance=(SELECT SUM(Amount) FROM tbl_payment)
FROM tbl_payment t
INNER JOIN INSERTED i
ON i.pid=t.pid
END

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 12:20:21

mean this table in on my LOCAL SYSTEM SQL SERVER..........

So ...i have all d permissions n all

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 12:23:50

remove dbo


CREATE TRIGGER Amount_update
ON tbl_payment
AFTER INSERT 
AS 
BEGIN
UPDATE t
SET t.balance=(SELECT SUM(Amount) FROM tbl_payment)
FROM tbl_payment t
INNER JOIN INSERTED i
ON i.pid=t.pid
END

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 12:29:07

ohk Thanksssss
it done
thaks to both of u

but noe i test it in tommorow morning,,,,,,

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 12:30:17

bcoz i think there is one thing we missing,,,,,,,,so i like to test it in morning ,,,,,
but i get success in makin that trigger...
Thanks to u all,,,,,,,,,,,,,,
Hope u all help me tommorow also?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 12:36:17

quote:
Originally posted by ashishashish

bcoz i think there is one thing we missing,,,,,,,,so i like to test it in morning ,,,,,
but i get success in makin that trigger...
Thanks to u all,,,,,,,,,,,,,,
Hope u all help me tommorow also?

sure

looking forward to hear from you

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-14 : 23:47:29

Thanks for your concern.........

CREATE TRIGGER Amount_update
ON tbl_payment
AFTER INSERT
AS
BEGIN
UPDATE t
SET t.balance=(SELECT SUM(Amount) FROM tbl_payment)
FROM tbl_payment t
INNER JOIN INSERTED i
ON i.pid=t.pid
END
In this trigger i also want that .......
mean i do this on the particular dealer_id.....
mean in my table there is a field a column named dealer_id... so i want to get balance of that particular Dealer_id so can i alter this trigger as...

CREATE TRIGGER Amount_update
ON tbl_payment
AFTER INSERT
AS
BEGIN
UPDATE t
SET t.balance=(SELECT SUM(Amount) FROM tbl_payment)
FROM tbl_payment t
INNER JOIN INSERTED i
ON i.pid=t.pid
and i.dealer_id=t.dealer_id
END

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-02-15 : 01:06:57

i Also like to pass a dealer_id in this because in my table there r several dealers n each dealer have many records so i have to do this sum operation on only a specific dealer mean if i insert a vlaue of dealer_id(487546) then there must be sum of all the amount associated to that dealer and the value is updated in the BALANCE column??
Thanks In Advance....
I Noe u must have something...

quote:
Originally posted by visakh16

quote:
Originally posted by ashishashish

bcoz i think there is one thing we missing,,,,,,,,so i like to test it in morning ,,,,,
but i get success in makin that trigger...
Thanks to u all,,,,,,,,,,,,,,
Hope u all help me tommorow also?

sure
looking forward to hear from you

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources