problem with updation of table ????
-
Friday, May 11, 2012 6:57 AM
hello evyone ,
i hve got 2 table sales n account.
when i got sale_cash(column name) in sales table , i want to update this cash imformation in my account table , current_account(column name) , automatically.
All Replies
-
Friday, May 11, 2012 7:00 AM
create after update trigger on table sales table and check if inserted.sale_cash is not null then update that value into account table with field "current_account".
Please vote if you find this posting was helpful or Mark it as answered.
- Proposed As Answer by Satheesh Variath Friday, May 11, 2012 7:28 AM
-
Friday, May 11, 2012 7:23 AM
Create a trigger for update/insert on sales table. Inside it, you detect any value change to further update account table. Read this also
http://msdn.microsoft.com/en-us/library/ms189799.aspx
Many Thanks & Best Regards, Hua Min
-
Friday, May 11, 2012 7:26 AM
use below query
Merge sale_cash AS dest using current_account AS src on src.id = dest.id WHEN Matched THEN UPDATE SET Column_name=src.column_name
nishantcomp2512
-
Friday, May 11, 2012 7:34 AM
Using trigger on your sales table is an option to do this,
You may consider the performance if table will grow really large or a huge updates to the table
Regards
Satheesh -
Saturday, May 12, 2012 6:29 AM
hey check this code , n correct it . yr advice is really helpfull.
sql is catching error "incorrect syntax near where".
table name is sale_payment and current_account.
create
trigger db2
on
[dbo].[sale_payment]
after
insert
as
begin
update
dbo.current_accounts
where
dbo.sale_payment.id =dbo.current_accounts.id
set
current_account = dbo.current_accounts.current_account +(select dbo.sale_payment.amount from inserted )
END
GO
-
Saturday, May 12, 2012 6:32 AM
try this :
update ca set current_account = ca.current_account + sp.amount from dbo.current_accounts ca inner join inserted sp ON sp.id = ca.id
Please vote if you find this posting was helpful or Mark it as answered.
- Edited by Paresh Patel Saturday, May 12, 2012 6:34 AM
- Edited by Paresh Patel Saturday, May 12, 2012 6:35 AM
- Marked As Answer by dpk89 Saturday, May 12, 2012 8:34 AM
-
Saturday, May 12, 2012 6:51 AM
its not working error "
Incorrect syntax near '.'.
"
create
trigger db2
on
[dbo].[sale_payment]
after
insert
as
beginupdate
dbo.current_accounts
set
current_account = dbo.current_accounts.current_account + dbo.sale_payment.amount
from dbo.current_accounts
inner join inserted dbo.sale_payment on dbo.sale_payment.id = dbo.current_accounts.id
END
GO
from
- Edited by dpk89 Saturday, May 12, 2012 6:53 AM
-
Saturday, May 12, 2012 6:56 AM
y do we need inner join , we don't hve to join two tables.
we just need to update a table with trigger when sale_payement table is updated or inserted some value.
-
Saturday, May 12, 2012 7:25 AM
inner join is used to update the record in current_account table based on id which is inserted or updated on sale_payment table:
try this :
update dbo.current_accounts set current_account = dbo.current_accounts.current_account + dbo.sale_payment.amount from dbo.current_accounts inner join dbo.sale_payment on dbo.sale_payment.id = dbo.current_accounts.id
Please vote if you find this posting was helpful or Mark it as answered.
- Marked As Answer by dpk89 Saturday, May 12, 2012 8:34 AM
-
Saturday, May 12, 2012 8:34 AM
why you expect to have more than one record ?
Regards,
Ahmed Ibrahim
SQL Server Setup Team
This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you.
This can be beneficial to other community members reading the thread. -
Saturday, May 12, 2012 9:04 AM
First, there is an obscure button in the web UI with two arrows pointing from each other. This buttom permits you insert code, without the web UI mangling the code.
Next, I would recommend that you make more effort to make yourself clear. That is, use capital letters, place periods and commas where they belong, spell out words. But also explain what problem you are trying to solve. A common recommendation is that you include CREATE TABLE statements for your tables, INSERT statements with sample data and the expected the result. And finally, always include which version of SQL Server you are using.
What I can tell from you have now, there is an important thing about the trigger that is missing. In a trigger you have access to two virtual tables. The table "inserted" holds the newly inserted rows in an INSERT trigger and the resulting updated rows in an UPDATE trigger. The table "deleted" holds the deleted rows in an DELETE trigger, and a before-image of the updated rows in an UPDATE trigger.
It is also important to understand that a trigger fires once per statement, and thus must be able to handle multiple rows.
If sale_payment.id really maps to current_accounts.id (which I'm skeptic to, because it would be a poor naming convention), you trigger should look like this:
CREATE TRIGGER sale_payment_tri ON sale_payment AFTER INSERT AS
BEGIN
; WITH aggr AS (
SELECT id, SUM(amount) AS amount
FROM inserted
GROUP BY id
)
UPDATE dbo.current_accounts
SET current_account = current_account + aggr.amt
FROM dbo.current_accounts ca
JOIN ca.id = aggr.id
END
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se -
Saturday, May 12, 2012 9:04 AM
well , m newbee . i understand things late.
here in sale_payment table id is unique n changes evytime ,
so m expecting if "inner join dbo.sale_payment on dbo.sale_payment.id = dbo.current_accounts.id" ,
current_accounts with d same id of sale_payment , should hve more records.
.png)
