CODEWITHSUNDEEP
sqlsql-serverif-statementtriggers
Argentina
Argentina

Reputation: 1141

SQL Server Create trigger: Incorrect syntax near the keyword 'IF'

I am trying to convert a db2 trigger in SQL Server. I have already changed some things as the NEWDOC and OLDDOC references and removed 'THEN' keywords.

I am still getting this error:

Incorrect syntax near ';'.
Incorrect syntax near the keyword 'IF'.

the SQL is:

-- <ScriptOptions statementTerminator="!" />
CREATE TRIGGER DOCUMENTS_UPDATE ON DOCUMENTS_VIEW
INSTEAD OF UPDATE 
AS 
BEGIN
  IF (select user_id from inserted) is null
    UPDATE DOCUMENTS 
    SET    id = (select id from inserted), 
           user_id = (SELECT id from users 
                      where username = (select username from inserted)
                     ), 
           code = (select code from inserted), 
           description = (select description from inserted), 
           type = (select type from inserted), 
           expiry = (select expiry from inserted), 
           parent_code = (select parent_code from inserted), 
           attributes = (select attributes from inserted), 
           acquired = (select acquired from inserted) 
    WHERE id = (select id from inserted);
  ELSE 
    UPDATE DOCUMENTS 
    SET    id = (select id from inserted), 
           user_id = (select user_id from inserted), 
           code = (select code from inserted), 
           description = (select description from inserted), 
           type = (select type from inserted), 
           expiry = (select expiry from inserted), 
           parent_code = (select parent_code from inserted), 
           attributes = (select attributes from inserted), 
           acquired = (select acquired from inserted) 
    WHERE id = (select id from inserted);
  END IF;

  UPDATE DOCUMENT_STATES 
  SET    document_id = (select id from inserted), 
         state = (select state from inserted), 
         date = (select date from inserted), 
         timestamp = (select timestamp from inserted) 
  WHERE document_id = (select id from inserted);

  IF NEWDOC.type = 'client_order' 
    UPDATE DOCUMENTS 
    SET    parent_code = (select code from inserted) 
    WHERE code = (select parent_code from inserted);
END IF;
END!

Upvotes: 0

Views: 1007

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Your syntax problem is easy to fix. As GandRalph points out, there is no END IF in SQL Server.

More importantly, you are not using inserted correctly. It can contain more than one row -- all rows affected by the DML statement. Your query will fail spectacularly with errors when that happens.

The first if can be replaced by a single update. It would look like this:

update d
    set  user_id = coalesce(u.id, i.userid),
         code = i.code, 
         description = i.description, 
         type = i.type, 
         expiry = i.expiry, 
         parent_code = i.parent_code, 
         attributes = i.attributes, 
         acquired = i.acquired 
    from documents d join
         inserted i
         on d.id = i.id left join
         users u
         on u.username = i.username

Upvotes: 0

cloudsafe
cloudsafe

Reputation: 2504

CREATE TRIGGER 
DOCUMENTS_UPDATE ON DOCUMENTS_VIEW
INSTEAD OF UPDATE 
AS 
BEGIN
    IF (select user_id from inserted) is null
        UPDATE DOCUMENTS SET id = (select id from inserted), user_id = (SELECT id 
        from users where username =(select username from inserted)), code = (select 
        code from inserted), description = (select description from inserted), type 
        = (select type from inserted), 
        expiry = (select expiry from inserted), parent_code = (select parent_code 
        from inserted), attributes=(select attributes from inserted), acquired=
        (select acquired from inserted) WHERE id = (select id from inserted);
    ELSE 
        UPDATE DOCUMENTS SET id = (select id from inserted), user_id = (select 
        user_id from inserted), code = (select code from inserted), description = 
        (select description from inserted), type = (select type from inserted), 
        expiry = (select expiry from inserted), parent_code = (select parent_code 
        from inserted), attributes=(select attributes from inserted), acquired=
        (select acquired from inserted) WHERE id = (select id from inserted);

    UPDATE DOCUMENT_STATES SET document_id =(select id from inserted), state = 
    (select state from inserted), date = (select date from inserted), timestamp 
     =  (select timestamp from inserted) WHERE document_id = (select id from 
    inserted);

    IF NEWDOC.type = 'client_order'
        UPDATE DOCUMENTS SET parent_code = (select code from inserted) WHERE code = 
        (select parent_code from inserted);

END

Upvotes: 0

GandRalph
GandRalph

Reputation: 635

There is no END IF in TSQL. Format should be:

IF <condition>
Begin
 --Do something
End
Else
Begin
 --Do something
End

Upvotes: 2

Related Questions