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
AUTONOMOUS TRANSACTION
Prior to Oracle8i, there was no way in which some SQL
operations within a transaction could be committed independent of the rest of
the operations. Oracle allows this, however, through autonomous transactions.
An autonomous transaction is a
transaction that is started within the context of another transaction, known as
parent transaction, but is independent of it. The autonomous transaction can be
committed or rolled back regardless ot the state of the parent transaction.
Ex:
CREATE OR REPLACE TRIGGER
AUTONOMOUS_TRANSACTION_TRIGGER
after insert on student
DECLARE
pragma autonomous_transaction;
BEGIN
update student set marks = 555;
commit;
END AUTONOMOUS_TRANSACTION_TRIGGER;
Output:
SQL> select * from student;
NO NA MARKS
----- ----- -- ----------
1
a 111
2
b 222
3
c 300
SQL>
insert into student values(4,'d',444);
SQL> select * from student;
NO NA MARKS
---- ------ -- ----------
1
a 555
2
b 555
3
c 555
4
d 444
RESTRICTIONS ON AUTONOMOUS TRANSACTION
1
If an autonomous transaction attempts to access a resource held by
the main transaction, a deadlock can occur in you program.
2
You cannot mark all programs in a package as autonomous with a
single PRAGMA declaration. You must indicate
autonomous transactions explicity in each program.
3
To exit without errors from an autonomous transaction program that
has executed at least one INSERT or
UPDATE or DELETE, you must perform an explicit commit
or rollback.
4
The COMMIT and ROLLBACK statements end the active autonomous
transaction, but they do not force the termination of the autonomous routine.
You can have multiple COMMIT
and/or ROLLBACK statements inside your autonomous
block.
5
You can not rollback to a savepoint set in the main transaction.
6
The TRANSACTIONS parameter in the oracle
initialization file specifies the maximum number of transactions allowed
concurrently in a session. The default value is 75 for this, but you can
increase the limit.
MUTATING TABLES
There are restrictions on the tables and columns that a
trigger body may access. In order to define these restrictions, it is necessary
to understand mutating and constraining tables.
A mutating table is table that is currentlty being modified
by a DML statement and the trigger event also DML statement. A mutating table
error occurs when a row-level trigger tries to examine or change a table that
is already undergoing change.
A constraining table is a table that might need to be read
from for a referential integrity constraint.
Ex:
CREATE OR REPLACE TRIGGER
MUTATING_TRIGGER
before delete on student
for each row
DECLARE
ct number;
BEGIN
select count(*) into ct from student
where no = :old.no;
END MUTATING_TRIGGER;
Output:
SQL> delete student where no = 1;
delete student where no = 1
*
ERROR at line 1:
ORA-04091: table SCOTT.STUDENT is
mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T",
line 4
ORA-04088: error during execution of
trigger 'SCOTT.T'
HOW TO AVOID MUTATING TABLE ERROR ?
1
By using autonomous transaction
2
By using statement level trigger
No comments:
Post a Comment