Msg 102, Incorrect syntax near ')'

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • @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.

  • 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!!

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply