CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
maciejka
maciejka

Reputation: 948

Oracle update trigger on the same table

I want to update field data_aktualizacji when some row in the same table is updated. I created the following compound trigger.

CREATE OR REPLACE TRIGGER oferta_update_trigger
FOR UPDATE ON oferty
compound TRIGGER
  id_oferty number(10);

  AFTER EACH ROW IS
    BEGIN
      id_oferty := :new.idk;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
    BEGIN
      UPDATE oferty SET data_aktualizacji = SYSDATE WHERE idk = id_oferty;
  END AFTER STATEMENT;
END;
/

When I want to update some record, I get the following error.

SQL Error: ORA-00036: maximum number of recursive SQL levels (50) exceeded.

How to solve this problem? I this that some loop is created, but I don't know, how to workaround this.

Upvotes: 0

Views: 924

Answers (2)

kara
kara

Reputation: 3455

As Ctznkane525 wrote, you definitively should use default-value to perform this action.

If you don't want to use default you can modify new.data_aktualizacji:

CREATE OR REPLACE TRIGGER oferty_update_aktualizacji
BEFORE INSERT OR UPDATE
ON oferty
FOR EACH ROW
DECLARE
BEGIN
   :new.data_aktualizacji:= sysdate;
END;

Upvotes: 0

Ctznkane525
Ctznkane525

Reputation: 7465

Update oracle to alter the column to default to sysdate

Alter table oferty alter column data_aktualizacji set default sysdate

No need for trigger at all

Upvotes: 1

Related Questions