INSTEAD OF Trigger control operation on view , not table. They can be used to make non-updateable views updateable and to override the behvior of view that are updateable.
Database triggers fire whenever the database startup or is shutdown, whenever a user logs on or log off, and whenever an oracle error occurs. these tigger provide a means of tracking activity in the database
Instead of trigger : A view cannot be updated , so if the user tries to update a view, then this trigger can be used , where we can write the code so that the data will be updated in the table, from which the view was created. Database trigger : this trigger will be fired when a database event ( dml operation ) occurs in the database table, like insert , update or delete. System triggers : this trigger will fire for database events like dtartup / shutdown of the server, logon / logoff of the user, and server errors ... and also for the ddl events, like alter, drop, truncate etc.
In database trigger: Trigger for a table /view is a database trigger
like ( before , insert * update, delete, insert * row level, statement level)
2 * 3 * 2 = 12 + instead off trigger for views
schema triggers reffers to : before logoff, after logon, before create, drop,alter on schema ( these trigger are also called DDL trigger)
Application trigger: before shutdown, after startup, on error( any error occur in database, after starting the orcalce instance, before closing the instance)
Difference between instead off, database and schema trigger
Hi
if we have created a view that is based on join codition then its not possibe to apply dml operations like insert, update and delete on that view. So what we can do is we can create instead off trigger and perform dml operations on the view.
Database Vs Schema trigger:
consider these three triggers
create or replace trigger trig_usrA_conn
after logon on schema
Begin
insert into example.temp_table values (1,' user A connection fired');
End;
create or replace trigger trig_usrB_conn
after logon on schema
Begin
insert into example.temp_table values(2.'user B connection fired');
End;
create or replace trigger trig_All_conn
after logon on database
Begin
insert into example.temp_table values(3,' All connection fired');
End;
we have created two schema trigger for usera A and B and then created a database trigger. The Database and Schema Keywords determine the level for a given system trigger.
Now you can connect to the database as usera A and then user B and example database. The after logon trigger on the schema fires first and then after logon trigger to the database
RE: Hi,Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger?Thanks.
Triggers on system events can be defined at the database or schema level.
A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.
INSTEAD OF Triggers
This type of trigger is used to provide a transparent way of modifying views that cannot be modified directly through SQL DML statements because the view is not inherently modifiable. You can write INSERT, UPDATE, and DELETE statements against the view. The INSTEAD OF trigger works invisibly in the background performing the action coded in the trigger body directly on the underlying tables.
