August 5, 2008 at 5:00 am
Please tell me what problem in which
CREATE TRIGGER UpDateAfterSupplierTransection on SupplierTransectionDetails
For Insert, Update, Delete
As
Update S
if D.Debit<>0
S.Balance= S.Balance + D.Debit
From Suppliers S Join Insert i
Error Generated:
Msg 156, Level 15, State 1, Procedure UpDateAfterSupplierTransection, Line 5
Incorrect syntax near the keyword 'if'.
Msg 102, Level 15, State 1, Procedure UpDateAfterSupplierTransection, Line 6
Incorrect syntax near 'S'.
August 5, 2008 at 9:45 am
You are referring to a table D (D.Debit) but you don't declare this table. Perhaps you mean I.Debit?
August 5, 2008 at 12:12 pm
Your code does not work because you reference the Insert table, which, unless you have created it in your database, does not exist. I think you want the virtual inserted table. You also reference D.debit and you have no tables aliased as D in your query. As AART said you probably mean the virtual deleted table. You are using an IF (control of flow statement) within an Update (DML statement) and that is not allowed. You also have no listed ON clause for your JOIN.
I think this is what you want:
[font="Courier New"]CREATE TRIGGER UpDateAfterSupplierTransection ON SupplierTransectionDetails
FOR INSERT, UPDATE, DELETE
AS
UPDATE S
S.Balance = S.Balance + D.Debit
FROM
Suppliers S JOIN
deleted D ON
S.pk = D.pk
WHERE
D.debit <> 0
[/font]
This assumed you want to set the Balance to Balance + Debit when the deleted Debit value is not 0. This code will not work on inserts and you need to remove any reference to the inserted table in order for it to work on deletes.
If you explain what you want to happen and give some example data (check out the links in my signature) someone will provide you with the correct answer.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply