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
0 comments:
Post a Comment