CODEWITHSUNDEEP
sqldatabaseoracle-database
Kayfi
Kayfi

Reputation: 11

Oracle trigger update values after insertion

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;
/

Upvotes: 0

Views: 2114

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18705

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.

Upvotes: 1

Related Questions