The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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
587182234
accept rate: 6%

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
29.3k287438644
accept rate: 32%

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:

×58

question asked: 23 May '14, 05:52

question was seen: 378 times

last updated: 23 May '14, 06:30