Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

This trigger updated every row in the table to with a new value after each insert

ALTER TRIGGER "tr_insert_eopi"
AFTER INSERT
ORDER 1
ON end_of_period_inventory
REFERENCING NEW as new_name
FOR EACH ROW
BEGIN

UPDATE end_of_period_inventory
SET reference_id = Cast( getDate() as char )
WHERE new_name.reference_id = '' or new_name.reference_id is null ;

END

asked 23 May '14, 05:52

Tom%20Mangano's gravatar image

Tom Mangano
672242738
accept rate: 8%

edited 23 May '14, 05:53


You are mixing a row-level AFTER INSERT with a statement-level UPDATE, so the fact that multiple rows are updated should not come as a surprise (and the UPDATE might be called several times in case you would insert several times at once, say by a INSERT SELECT)...

If you want to alter the contents of a row during the insert, I'd highly recommend a BEFORE INSERT trigger. That would simply have a body with something like

if isnull(new_name.reference_id, '') = '' then
   set new_name.reference_id = cast( getDate() as char )
end if;

and all should be fine...


If you prefer to use an AFTER INSERT trigger, make sure to restrict the UPDATE statement to a single row by filtering on the primary key of the just inserted row.

permanent link

answered 23 May '14, 06:26

Volker%20Barth's gravatar image

Volker Barth
40.4k364553825
accept rate: 34%

edited 23 May '14, 06:30

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:

×79

question asked: 23 May '14, 05:52

question was seen: 1,565 times

last updated: 23 May '14, 06:30