Similar to Instead-of Insert trigger, which we discussed in the previous tutorial, Instead-of Update trigger overrides default behavior of Update DML operation executed on the view. Execution of Update DML on a complex view is restricted because of the involvement of multiple tables over which your view is created. To override this restriction we can take the help of Instead-Of Update trigger.

Instead-Of Update Trigger

Instead-of update trigger will override the default behavior of your update operation when you execute the update statement and will let you update the data of the underlying tables over which your view is created.

Example:

Tables (Trainer and Subject) and View (VW_RebellionRider) used in this example are the same as the ones we created in the previous tutorial.

CREATE OR REPLACE TRIGGER io_update
INSTEAD OF UPDATE ON vw_rebellionrider
FOR EACH ROW
BEGIN
  UPDATE trainer SET FULL_NAME = :new.full_name 
  WHERE FULL_NAME = :old.full_name;
  UPDATE subject SET subject_NAME = :new.subject_name 
  WHERE subject_NAME = :old.subject_name;
END;
/

On successful execution this trigger will let you execute an Update DML on the view.

Hope you enjoyed reading. Help others in learning the concepts by sharing this blog on your Social media. Thanks and have a great day!