cancel insert from a before insert trigger

[Former Member]

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.

[Breck_Carter]

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.

[MCMartin]

For a non autoincrement primary key you can use the "Insert on exsting update" statement in SQLA to achieve the desired behavior

[VolkerBarth]

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:

MERGE INTO ABC
USING mySourceTable ON ABC.hostname = mySourceTable.hostname
WHEN NOT MATCHED THEN INSERT
WHEN MATCHED THEN UPDATE;

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