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