|
Hi, I want to use the before update trigger in data into data which have change for example |
|
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) * ... |
So what is your question?
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