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

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related