Hello, I have a table in which if a row is tried to insert the trigger should check if the desired row exists then update some column and cancel the insert. I am using below code for trigger: ALTER TRIGGER "delete_entry_before_inserting" BEFORE INSERT ORDER 1 ON "XYZ"."ABC" REFERENCING NEW AS "inserted" FOR EACH ROW /* WHEN( search_condition ) */ BEGIN IF EXISTS (select hostname from ABC WHERE hostname = inserted.hostname) THEN UPDATE ABC SET days_count = (days_count + 1) WHERE hostname = inserted.hostname ROLLBACK TRANSACTION END IF END but sqlanywhere is complaining that it has syntax error. Can anyone please tell me whats wrong with the above trigger statement. |
For a non autoincrement primary key you can use the "Insert on exsting update" statement in SQLA to achieve the desired behavior |
In addition to Martin's answer: In case "hostname" is not the primary key column: The MERGE statement (available since v11) can handle "insert or update" for arbitrary filter conditions, as well, cf. this frame of a sample:
Note, this is a very simple sample for MATCH, and it uses a lot of "defaults" and shortcuts - I would think that's good practise to use the more verbose clauses to really explain what the matched / not matched actions should do - MERGE is a very powerful statement... |
Martin's given the answer to the real question.
As for the syntax error, it may be that a semicolon is required after the UPDATE, before the ROLLBACK.
Plus... it will fail at runtime because triggers can't contain a rollback or commit: http://dcx.sybase.com/index.html#1201/en/saerrors/errm273.html
To rollback a trigger and the triggering operation, see ROLLBACK TRIGGER http://dcx.sybase.com/index.html#1201/en/dbreference/rollback-trigger-statement.html
BUT... none of that is necessary; see Martin's answer.