INSTEAD OF Triggers
INSTEAD OF triggers control insert, update, and delete operations on views, not tables. They can be used to make non-updateable views updateable and to override the default behavior of views that are updateable.
Creating an INSTEAD OF Trigger
To create (or replace) an INSTEAD OF trigger, use the syntax shown here:
1 CREATE [OR REPLACE TRIGGER]trigger_name2 INTEAD OFoperation3 ONview name4 FOR EACH ROW 5 BEGIN 6 ...code goes here... 7 END;
The table contains an explanation of this code:
|
Line(s) |
Description |
|---|---|
|
1 |
States that a trigger is to be created with the unique name supplied. Specifying OR REPLACE is optional. If the trigger exists and REPLACE is not specified, then your attempt to create the trigger anew will result in an ORA-4081 error. |
|
2 |
This is where we see differences between INSTEAD OF triggers and other types of triggers. Because INSTEAD OF triggers aren’t really triggered by an event, we don’t need to specify AFTER or BEFORE or provide an event name. What we do specify is the operation that the trigger is to fire in place of (or instead of). Stating INSTEAD OF followed by one of INSERT, UPDATE, or DELETE accomplishes this. |
|
3 |
This line is somewhat like the corresponding line for DDL and database event triggers in that the keyword ON is specified. The similarities end there: instead of specifying DATABASE or SCHEMA, we provide the name of the view to which the trigger is to apply. |
|
4-7 |
Contains standard PL/SQL code. |
INSTEAD OF triggers ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access