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