PLSQL-SYSTEM TRIGGERS

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

SERVERERROR

The SERVERERROR event can be used to track errors that occur in the database. The error code is available inside the trigger through the SERVER_ERROR attribute function.

Ex:

SQL> create table my_errors(error_msg varchar(200));

CREATE OR REPLACE TRIGGER SERVER_ERROR_TRIGGER

        after servererror on database

BEGIN

        insert into my_errors values(dbms_utility.format_error_stack);

END SERVER_ERROR_TRIGGER;

Output:

SQL> create table ss (no));

create table ss (no))

                    *

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> select * from my_errors;

ERROR_MSG

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

ORA-00922: missing or invalid option

SQL> insert into student values(1,2,3);

insert into student values(1,2,3)

            *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from my_errors;

ERROR_MSG

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

ORA-00922: missing or invalid option

ORA-00942: table or view does not exist

SERVER_ERROR ATTRIBUTE FUNCTION

It takes a single number type of argument and returns the error at the position on the error stack indicated by the argument. The position 1 is the top of the stack.

Ex:

CREATE OR REPLACE TRIGGER SERVER_ERROR_TRIGGER

        after servererror on database

BEGIN

        insert into my_errors values(server_error(1));

END SERVER_ERROR_TRIGGER;

SUSPEND TRIGGERS

This will fire whenever a statement is suspended. This might occur as the result of a space issue such as exceeding an allocated tablepace quota. This functionality can be used to address the problem and allow the operatin to continue.

Syntax:

Create or replace trigger <trigger_name>

after  suspend on {database | schema}

[When (…)]

[Declare]

            -- declaration section

Begin

            -- trigger body

[Exception]

            -- exception section

          End <trigger_name>;

Ex:

    SQL> create tablespace my_space datafile 'f:\my_file.dbf' size 2m;

    SQL> create table student(sno number(2),sname varchar(10)) tablespace my_space;

CREATE OR REPLACE TRIGGER SUSPEND_TRIGGER

      after suspend on database

BEGIN

      dbms_output.put_line(‘ No room to insert in your tablespace');

END SUSPEND_TRIGGER;

Output:

        Insert more rows in student table then , you will get

        No room to insert in your tablespace