CODEWITHSUNDEEP
oracle-databaseplsql
civesuas_sine
civesuas_sine

Reputation: 119

oracle trigger on update

CREATE TABLE COUNTRY
(
  COUNTRY_ID            VARCHAR2(2 BYTE)        NOT NULL,
  NAME                  VARCHAR2(200 BYTE),  
  CURRENCY_ID           VARCHAR2(3 BYTE)        DEFAULT 'EUR'

)

CREATE TABLE CURRENCY
(
  CURRENCY_ID      NUMBER                       NOT NULL,--this is sequnce
  NAME             VARCHAR2(20 BYTE),
  VALUE            NUMBER,
  BANK_PERCENTAGE  NUMBER
)


CREATE OR REPLACE TRIGGER update_currency
AFTER UPDATE
   ON SOFTDEV.COUNTRY
   FOR EACH ROW
DECLARE
BEGIN
   if :new.currency_id = :old.currency_id
   then
     null; -- if the currency_id exists then to nothing
    else
   INSERT INTO CURRENCY
   ( CURRENCY_ID,
     NAME,
     VALUE,
     BANK_PERCENTAGE)

   VALUES --line 20
   ( CURRENCY_ID_SEQ.nextval,
     :new.currency_id,
     1,
     0);
         end if;
END;

I have this trigger on update which works fine until there is no currency_id which is same as old currency.. When i try to insert currency which exists i get error . UPDATE_CURRENCY", line 20 ORA-04088: error during execution of trigger 'UPDATE_CURRENCY'

How will I resolve issue with already existed currency_id ?

Upvotes: 0

Views: 38

Answers (1)

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

Hard to tell what might be causing your error. But I do suggst you change your approach - and not use a trigger. You should avoid executing non-query DML like inserts in your table triggers. That can lead to all sorts of problems. Here's a good starting point for exploring the issues: https://www.oracle.com/technetwork/testcontent/o58asktom-101055.html

I suggest you instead create a procedure that is called to update a row in the table. In that procedure, you can check to see if the currency is changing. If so, execute the insert as part of the transaction in that procedure.

Then grant execute on the package containing that procedure to developer schemas, but don't let them update the table directly.

That way you ensure that the full transaction, with all your error handling and recovery logic is always run when a row is updated.

Upvotes: 2

Related Questions