Oracle trigger update values after insertion

Question

I am working on creating a trigger, but still stuck and not sure what is wrong with the code. Any help is appreciated.

The trigger BI_FILM_DESP appends text to the description of every new film inserted into the db. Output should be something like this [description].[rating] : Originally in <original_langauge_id>. Re-released in <language_id>. If rating, language id, or original language is null, the film would use the original description.

CREATE OR REPLACE TRIGGER "BI_FILM_DESP"
AFTER INSERT ON "FILM"
FOR EACH ROW
DECLARE
DESCRIPTION VARCHAR2 (255);
BEGIN
INSERT INTO FILM
(TITLE, DESCRIPTION, LANGUAGE_ID, ORIGINAL_LANGUAGE_ID, RATING) VALUES (:new.TITLE, :new.DESCRIPTION, :new.LANGUAGE_ID, :new.ORIGINAL_LANGUAGE_ID, :new.RATING)
UPDATE FILM 
SET DESCRIPTION = DESCRIPTION '. '  RATING  ': Originally in '  LANGUAGE_ID  '. RE-released in '  ORIGINAL_LANGUAGE_ID
WHERE RATING IS NOT NULL
OR LANGUAGE_ID IS NOT NULL
OR ORIGINAL_LANGUAGE_ID IS NOT NULL;
END;
/

Koen Lostrie · Accepted Answer · 2021-10-21 15:46:17Z

That is not how a trigger works. DML statements on the table that the trigger is created on are not possible. Instead do something like this:

CREATE OR REPLACE TRIGGER "BI_FILM_DESP" BEFORE
  INSERT ON "FILM"
  FOR EACH ROW
DECLARE
  l_description VARCHAR2(255);
BEGIN
  IF ( :new.rating IS NOT NULL OR :new.language_id IS NOT NULL OR :new.original_language_id IS NOT NULL ) THEN
    :new.description := :new.description
                        || '. '
                        || :new.rating
                        || ': Originally in '
                        || :new.language_id
                        || '. RE-released in '
                        || :new.original_language_id;

  END IF;
END;
/

a BEFORE INSERT trigger is what you want, because you're modifying a column before it is inserted
You don't need a trigger for this functionality. Same functionality can be achieved using a virtual column.
contatenation in oracle is done using the || symbol.
makes sense to prefix your variables. A variable with the same name as a column of a table is asking for problems. I renamed description to l_description
You might want to read up on triggers. A great place to start is a blog like this one.

Collectives™ on Stack Overflow

Oracle trigger update values after insertion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related