Oracle PL-SQL

Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

Insert Update Delete

Before Row o.k. o.k. o.k.

After Row o.k. o.k. o.k.

Before Statement o.k. o.k. o.k.

After Statement o.k. o.k. o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.

If WHEN clause is specified, the trigger fires according to the returned Boolean value.

It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.

yes WE can use COMMIT and ROLLBACK triggers, but by using PRAGAMA AUTONAMOUS_TRANSATIONS. Now the transation treated as a autonomous tansation.

The table columns are referred as OLD.column_name and NEW.column_name.

For triggers related to INSERT only NEW.column_name values only available.

For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.

For triggers related to DELETE only OLD.column_name values only available.

Mutation of table occurs.

I & ii.

Exception :

Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are.

CURSOR_ALREADY_OPEN

DUP_VAL_ON_INDEX

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

INVALID_NUMBER

LOGON_DENIED

NOT_LOGGED_ON

PROGRAM-ERROR

STORAGE_ERROR

TIMEOUT_ON_RESOURCE

VALUE_ERROR

ZERO_DIVIDE

OTHERS.

exception is an identifier and error handling part of pl/sql types := 1)predifined

2) user defined.

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database

trigger.

SQLCODE returns the latest code of the error that has occurred.

SQLERRM returns the relevant error message of the SQLCODE.

In the standard package.

Procedures, Functions & Packages ;

A stored procedure is a sequence of statements that perform specific function.

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

eg. FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal;

END LOOP;

%ISOPEN - To check whether cursor is open or not

% ROWCOUNT - Number of rows fetched/updated/deleted.

% FOUND - To check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND - To check whether cursor has fetched any row. True if no rows are featched.

These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.

DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries. User defined cursors are called Explicit Cursors. They can be declared and used.

Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

Objects of type TABLE are called "PL/SQL tables", which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

Cursors

A FUNCTION is always returns a value using the return statement.

A PROCEDURE may return one or more values through parameters or may not return at all.

Extensibility,Modularity, Reusability, Maintainability and one time compilation.