INSTEAD-OF TRIGGERS

INSTEAD-OF TRIGGERS

Instead-of triggers fire instead of a DML operation. Also, instead-of triggers can be defined only on views. Instead-of triggers are used in two cases:

Ø  To allow a view that would otherwise not be modifiable to be modified.

Ø  To modify the columns of a nested table column in a view.

SYSTEM TRIGGERS

System triggers will fire whenever database-wide event occurs. The following are the database event triggers. To create system trigger you need ADMINISTER DATABASE TRIGGER privilege.

Ø  STARTUP

Ø  SHUTDOWN

Ø  LOGON

Ø  LOGOFF

Ø  SERVERERROR

Syntax:

Create or replace trigger <trigger_name>

{Before | after}  {Database event} on {database | schema}

[When (…)]

[Declare]

            -- declaration section

Begin

            -- trigger body

[Exception]

            -- exception section

          End <trigger_name>;

Ex:

SQL> create table user_logs(u_name varchar(10),log_time timestamp);

CREATE OR REPLACE TRIGGER AFTER_LOGON

     after logon on database

BEGIN

     insert into user_logs values(user,current_timestamp);

END AFTER_LOGON;

Output:

SQL> select * from user_logs;

        no rows selected

SQL> conn saketh/saketh

SQL> select * from user_logs;

U_NAME     LOG_TIME

---------- ------------------------------------------------

SAKETH     22-JUL-07 12.07.13.140000 AM

SQL> conn system/oracle

SQL> select * from user_logs;

U_NAME     LOG_TIME

---------- ------------------------------------------------

SAKETH     22-JUL-07 12.07.13.140000 AM

SYSTEM     22-JUL-07 12.07.34.218000 AM

SQL> conn scott/tiger

SQL> select * from user_logs;

U_NAME     LOG_TIME

---------- -----------------------------------------------

SAKETH     22-JUL-07 12.07.13.140000 AM

SYSTEM     22-JUL-07 12.07.34.218000 AM

SCOTT      22-JUL-07 12.08.43.093000 AM