CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
MayankG
MayankG

Reputation: 57

Trigger on a view in oracle

I have a basic doubt..Can i use a trigger to insert changes on a view into a NEW table?

example

create or replace trigger iam_insert
  instead of insert on test123
  FOR EACH ROW

BEGIN

  if inserting then

.
.
.
  end if;

end;

Regards

Upvotes: 2

Views: 5450

Answers (2)

JoshL
JoshL

Reputation: 10998

Yes, INSTEAD OF triggers are designed for this. Note that if you define an INSTEAD OF trigger on a view and then perform a data operation (such as an INSERT) on the view, your trigger will run in place of the operation. Oracle will not automatically insert the data as it would without the trigger - that is now your responsibility.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231791

Yes, that's the point of an INSTEAD OF trigger on a view. You can transform an INSERT against a complex view into any sort of DML operation on a base table (or on a table that the view doesn't even reference).

In general, though, it is pretty rare to encounter a situation where an INSTEAD OF trigger is really appropriate. They certainly exist, it's just rare. If you are trying to allow inserts into the view to insert data into the base tables of the view, I would make absolutely certain that you can't ensure that the view itself is key-preserved which would allow you to do DML against the view without needing to define a trigger.

Upvotes: 3

Related Questions