Hi, I want to use the before update trigger in data into data which have change for example

asked 24 Apr '15, 01:33

mfkpie8's gravatar image

mfkpie8
85424852
accept rate: 10%

edited 24 Apr '15, 08:03

Mark%20Culp's gravatar image

Mark Culp
23.2k9132272

1

So what is your question?

(24 Apr '15, 03:37) Volker Barth
Replies hidden

Think you can give me a Before update syntax! The following statement cannot perform the update:

ALTER TRIGGER "zx_shipper_line_xime" 
   BEFORE UPDATE OF  "user_9", "user_10"
   ORDER 6 ON "DBA"."shipper_line"
   REFERENCING  NEW AS new_shipper_line
FOR EACH ROW /* WHEN */
BEGIN
   update shipper_line
      set shipper_line.amount = isnull(shipper_line.user_9,0)*isnull(shipper_line.user_10,0),
          shipper_line.sys_all_amt =isnull(shipper_line.user_9,0)*isnull(shipper_line.user_10,0),
          shipper_line.sys_amount =isnull(shipper_line.user_9,0)*isnull(shipper_line.user_10,0),
          shipper_line.all_amt = isnull(shipper_line.user_9,0)*isnull(shipper_line.user_10,0),
          create_time = getdate(*)
    where shipper_line.trans_no = new_shipper_line.trans_no 
      and shipper_line.line_no = new_shipper_line.line_no 
      and isnull(shipper_line.user_10,0) >0;
END
(24 Apr '15, 04:12) mfkpie8

When using row level triggers (and BEFORE triggers are always row level triggers), you do not use UPDATE statements to modify the contents of the according row but use SET statements to modify the individual columns of the current row - cf. that sample from the docs:

CREATE TRIGGER emp_upper_postal_code
BEFORE UPDATE OF PostalCode
ON Employees
REFERENCING NEW AS new_emp
FOR EACH ROW
WHEN ( ISNUMERIC( new_emp.PostalCode ) = 0 )
BEGIN
   -- Ensure postal code is uppercase (employee might be 
   -- in Canada where postal codes contain letters)
   SET new_emp.PostalCode = UPPER(new_emp.PostalCode)
END;

So in your case it might work to use something like (I don't claim to understand the calculations):

   ...
   set new_shipper_line.amount = isnull(new_shipper_line.user_9,0)  * isnull(new_shipper_line.user_10,0);
   set new_shipper_line.sys_all_amt = isnull(new_shipper_line.user_9,0) * ...
permanent link

answered 24 Apr '15, 07:14

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 24 Apr '15, 07:26

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

question asked: 24 Apr '15, 01:33

question was seen: 365 times

last updated: 24 Apr '15, 08:06