April 11, 2016 at 7:48 pm
I have created a trigger, which after getting the inserted values in variables tries to insert in another table.
following is the insert query which is giving error.
insert into tblDoctorPatient (dpID,dpPatID,dpDocID,dp_psID)
values (@dpID,@psPatID,@dpDocID,@psID)
when I am trying to execute the trigger I am getting the error mentioned below:
Msg 102, Level 15, State 1, Procedure tr_insertDoctorPatient, Line 35
Incorrect syntax near ')'.
Please help.
April 11, 2016 at 8:22 pm
prachiverma10 (4/11/2016)
I have created a trigger, which after getting the inserted values in variables tries to insert in another table.following is the insert query which is giving error.
insert into tblDoctorPatient (dpID,dpPatID,dpDocID,dp_psID)
values (@dpID,@psPatID,@dpDocID,@psID)
when I am trying to execute the trigger I am getting the error mentioned below:
Msg 102, Level 15, State 1, Procedure tr_insertDoctorPatient, Line 35
Incorrect syntax near ')'.
Please help.
you didn't show all the code from your trigger; it specifically says line 35.
Additionally, since you are declaring @variables in a trigger, we know it is written incorrectly and cannot handle multiple rows correctly.
you'll need to rewrite it, you are going to lose data EVERY time an command handles more than one row
if you double click the error, ssms will take you to that line, but you must be missing something...another parenthesis, a keyword, the END command for the trigger, something, but you;ll need to post the whole proc if you cannot find it yourself.
Lowell
April 11, 2016 at 10:53 pm
Take a look at this link, this link will show you cause of Syntax error and how to fix it: http://www.sqlmdfviewer.org/fix-error-sql-syntax.html
April 12, 2016 at 11:27 am
johnwalker10 (4/11/2016)
Take a look at this link, this link will show you cause of Syntax error and how to fix it: http://www.sqlmdfviewer.org/fix-error-sql-syntax.html
Thank you so much for the link!!
April 12, 2016 at 11:32 am
prachiverma10 (4/12/2016)
@Lowell, Thank you so much for your help. I did miss to end the if statement.Regarding the other issue you pointed out, can you give an example in what scenario multiple insert can happen in one command, and how can I tackle that in my trigger.
Can't help without seeing the code.
as to how a trigger can see multiple rows, a simple update will do it:
UPDATE MyTable SET Notes = Replace(Notes,CHAR(13),'')
in general, a trigger should never declare variables.
here's a simple trigger example; because it uses the pseudo tables INSERTED and DELETED, it will do work on All rows that were updated, instead of the arbitrary single row you are doing when you assign your @variables.
CREATE TRIGGER TR_MYADDRESSES
ON MYADDRESSES
AFTER UPDATE AS
BEGIN
INSERT INTO ADDRESSCHANGES(EMPNO, COLCHANGEDBITMASK, OLDENAME, OLDADDR1, OLDADDR2, OLDCITY, OLDSTATECODE, OLDZIPCODE, OLDPHONE, NEWENAME, NEWADDR1, NEWADDR2, NEWCITY, NEWSTATECODE, NEWZIPCODE, NEWPHONE)
SELECT
DELETED.EMPNO,
COLUMNS_UPDATED(),
DELETED.ENAME,
DELETED.ADDR1,
DELETED.ADDR2,
DELETED.CITY,
DELETED.STATECODE,
DELETED.ZIPCODE,
DELETED.PHONE,
INSERTED.ENAME,
INSERTED.ADDR1,
INSERTED.ADDR2,
INSERTED.CITY,
INSERTED.STATECODE,
INSERTED.ZIPCODE,
INSERTED.PHONE
FROM DELETED
INNER JOIN INSERTED
ON DELETED.EMPNO = INSERTED.EMPNO --the primary key of the table!!!!!!
END; --TRIGGER
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply