Forum Stats

  • 3,816,037 Users
  • 2,259,133 Discussions
  • 7,893,366 Comments

Discussions

Create Compound Trigger to Make View Editable

User_9VHGT
User_9VHGT Member Posts: 2 Green Ribbon
edited May 7, 2021 8:10PM in General Database Discussions

Hello, I really need your help.

I have to create a Compound trigger that will make the view editable: the trigger should allow to change or add multiple rows at the same time with a single command.

Currently, I have the code below (after executing UPDATE operation there is an error ORA-01722: invalid number).

CREATE OR REPLACE TRIGGER trg

FOR INSERT OR UPDATE ON my_view

COMPOUND TRIGGER

INSTEAD OF EACH ROW IS

BEGIN

IF UPDATING THEN

IF (:NEW."Last_Name" != :OLD."Last_Name") THEN

UPDATE my_view

SET "Last_Name" = :NEW."Last_Name";

ELSIF (:NEW."First_Name" != :OLD."First_Name") THEN

UPDATE my_view

SET "First_Name" = :NEW."First_Name";

ELSIF (:NEW."Salary" != :OLD."Salary") THEN

UPDATE my_view

SET "Salary" = :NEW."Salary";

END IF;

END IF;

END;

P.S. The view contains three columns: "Last_Name", "First_Name" and "Salary".