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.

asked 29 Feb '12, 06:02

kailash19's gravatar image

kailash19
61123
accept rate: 0%

edited 29 Feb '12, 10:38

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668

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.

(29 Feb '12, 08:46) Breck Carter

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

permanent link

answered 29 Feb '12, 06:41

Martin's gravatar image

Martin
8.6k117151237
accept rate: 14%

edited 29 Feb '12, 06:41

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

permanent link

answered 29 Feb '12, 10:35

Volker%20Barth's gravatar image

Volker Barth
30.9k311457668
accept rate: 32%

edited 29 Feb '12, 10:56

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×60
×29

question asked: 29 Feb '12, 06:02

question was seen: 3,089 times

last updated: 29 Feb '12, 10:56