sunnyjsr

 Profile Answers by sunnyjsr 

• Oct 21st, 2006

 

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

deepak

• Oct 22nd, 2006

 

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.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 5th, 2006

 

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)

Nasim

• Dec 17th, 2006

 

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

connect userA/userA

connect userB/userB

connect example/example

g_sidhu

 Profile 

• Feb 4th, 2008

 

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.