Error in SQL coding

Tags:
Code
SQL
Triggers
I am having a specific problem with coding in SQL, and I was curious if you could give me any advice. I have entered this code: CREATE TRIGGER UpdateAfterPayment On SupplierTransactionDetails For Insert, Update, Delete As Update S If i.DebitValue>0 S.Balance = S.Balance + i.DebitValue else S.Balance = S.Balance - i.CreditValue from SupplierTransactionHead H, Suppliers S join inserted i On S.SupplierID=H.SupplierID and H.transectionID=i.TransectionID And I am receiving this error: Msg 102, Level 15, State 1, Procedure UpdateAfterPayment, Line 6 Incorrect syntax near 'S' Could you tell me why?
ASKED: August 4, 2008  2:31 PM
UPDATED: August 4, 2008  8:29 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Since the prior responce had nothing to do with the problem, I’ve removed it.

I think you’ll find this code works for you (be sure to test it before putting it into production, I don’t have any data to test against).

<pre>CREATE TRIGGER UpdateAfterPayment On SupplierTransactionDetails
For Insert, Update, Delete
As
Update Suppliers
S.Balance = case when i.DebitValue>0 then
S.Balance + i.DebitValue
else
S.Balance – i.CreditValue
end
from SupplierTransactionHead H
join inserted i On H.transectionID=i.TransectionID
where Suppliers.SupplierID=H.SupplierID
GO</pre>

You’ll see that I removed the Suppliers table from the FROM field. It should be up as part of the UPDATE and then referenced via the WHERE to link the values in Suppliers and SupplierTransactionHead together.

I’ve also removed the IF from the update statement. You need to use the CASE statement instead. When using the CASE statement within an update you specify the destination column = then the case. You’ll see that I’ve for when the DebitValue > 0 all the DebitValue, otherwise subtract the Credit Value (make sure I’ve got that correct).

You may find this article on the <a href=”http://itknowledgeexchange.techtarget.com/sql-server/back-to-basics-the-update-statement/”>UPDATE</a> statement useful.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following