PLSQL-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 theSERVER_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
Share on Google Plus

About Mindsforest

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment