How to use a trigger to update another table

Question

0.00/5 (No votes)

See more:

tblBudget(butID,butName,butAmount,IncurredTD_Amount)
tblInvoice(ID,ItemName,InvoiceAmount,butID)
here are my two tables, when i update the invoice table i would have a trigger kick in on any insert or update on invoice, that will reduced the budget amount intblbutget and also increase the INcurreTDAmount auomatically using the butID as reference in the tblInvoice(butAmount-InvoiceAmount,and IncurredTDAmount+INvoiceAmount)......is this possible? what are the performance issues if at all any? how can it be done.....thanks a million in advance

What I have tried:

AFTER INSERT,  UPDATE
AS
BEGIN
       SET NOCOUNT ON;
 
       DECLARE @Inv_Amount float,@Bud_ID int
 
       SELECT @Bud_ID = INSERTED.Bud_ID       
       FROM INSERTED
 
       INSERT INTO dbo.tblBudget(Bud_Amount,IncurTD_Amount,ID) IncurTD_Amount
       VALUES(Bud_Amount - @Inv_Amount, IncurTD_Amount + @Inv_Amount,'Inserted')
END

but i get an error:

Posted 27-Jun-17 23:46pm

Rodger007x

Updated 28-Jun-17 1:34am

Add a Solution

Comments

[no name] 28-Jun-17 6:26am

Seems there was an issue to post the error message, I only can see "but i get an error: ". Makes sense to add the error message ;)

Richard Deeming 29-Jun-17 17:57pm

Inserts, updates and deletes can affect more than one row. When they do, the trigger is fired once per statement, and the inserted and deleted virtual tables will contain multiple rows. Your trigger should be using set-based processing to handle this.

Also, your INSERT statement is not valid. I'm not sure whether that's a typo in your question?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2017-06-28T01:15:00

Solution 1

SQL

CREATE TRIGGER TR_Budget ON [dbo].[tblInvoice] 
	FOR INSERT, UPDATE
	AS
	BEGIN
          -- your code here, update tblBudget
	END

Posted 28-Jun-17 1:15am

RickZeeland

Updated 28-Jun-17 1:17am

v2

Rodger007x · Answer 2 · 2017-06-28T01:34:00

Solution 2

its complaninig about the coloumn name that dont match in the insert statement:

Msg 207, Level 16, State 1, Line 1

which is not possible as the i am referencing the right table but i guess i am not too well versed with SQL. Maybe a temporary table will do? dont know.....

Posted 28-Jun-17 1:34am

Rodger007x

Comments

RickZeeland 28-Jun-17 8:39am

Please use the comment button to add comments.
Do you see more error information ? could be some constraint that a field must be unique (often used on ID field).