after update trigger on a row.

[TheDash]

Hello! I am implementing a trigger that should do two actions when an update of a fields is performed on Table A from value 2 to 1.

1) Delete the record with same key combination in Table B
2) Clear the timestamp field of that row in Table A.

Here's what I coded as two sql statements first delete and then update, is this trigger ok otherwise please let me know your suggestions

CREATE OR REPLACE TRIGGER Trigger_1
AFTER UPDATE ON TableA
FOR EACH ROW
BEGIN
if ld.FK_O = 2 and :new.FK_O = 1 then
DELETE FROM TableB
WHERE ID = :new.ID
AND NO = :new.NO
AND NM = :new.NM;
UPDATE TableA SET Timestamp = NULL;
end if;
commit;
END;
/

Thanks
The Dash

 

[SantaMufasa]

Dash,

If you are updating the same row that caused the trigger to fire, you should use a BEFORE UPDATE trigger.

Also, you didn't mention in your posting what you wanted us to help with.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

Your trigger is incorrect. Use

CREATE OR REPLACE TRIGGER Trigger_1
  before UPDATE ON TableA
  FOR EACH ROW
BEGIN
if :old.FK_O = 2 and :new.FK_O = 1 then
      DELETE FROM TableB
      WHERE ID = :old.ID
      AND NO = :old.NO
      AND NM = :old.NM;
      :new.timestamp := null;
end if;
END;
/

You may NOT commit in a trigger and timestamp is a reserved word in oracle and should not be used for a column name.


Bill
Oracle DBA/Developer
New York State, USA