DBMS SQL DAY 20

DAY - 20

TRIGGERS AND CURSORS

WHAT IS CURSORS

A cursor is a pointer to this context area.

Types of cursors: There are two types of cursors:

(i)                  Implicit Cursor: Implicit Cursor The oracle implicitly (internally or automatically) opens a cursor for each SQL statement. Since the implicit cursors are opened and managed by oracle internally. So there is no need to open and manage the cursors by the user. Implicit cursor attributes can be used to access information about status of last insert, update, delete or single row select statement.

(ii)                Explicit Cursor: When individual records in a table have to be processed inside a PL/SQL code block a cursor is used. This cursor will be declared and mapped to an SQL query in the Declare Section of the PL/SQL block and used within the Executable Section. This cursor needs to be opened before the reading of the rows can be done, after which the cursor is closed. Cursor marks the current position in an active set. A cursor thus created and used is known as an Explicit Cursor.

EXAMPLE

Table Name:   Empno Deptno SaL

                             100 10 5000

                             101 30 6000

                             102 20 4000

                             103 30 5500

DECLARE

 CURSOR c_emp IS

 SELECT empno,sal

FROM emp

WHERE deptno=30;

 BEGIN

FOR rec IN c_emp

LOOP

UPDATE emp

SET sal=rec.sal+(rec.sal * .05)

WHERE empno=rec.empno;

 END LOOP;

 COMMIT;

 END;

WHAT IS TRIGGERS

A trigger is a stored procedure that is automatically fired (executed) when an INSERT, UPDATE or DELETE statements issued against the associated table. The trigger is not explicitly called by user. A trigger defines an action the database should take when some database related events occur. A trigger can include SQL and PL/SQL statements to execute it as a unit and it can invoke other stored procedures.

 Syntax:

CREATE [or REPLACE] TRIGGER trigger_name

 BEFORE | AFTER

 DELETE OR INSERT OR UPDATE [OF column_name,…]

 ON table_name

 [FOR EACH ROW [WHEN condition]]  } Triggering Restriction

DECLARE

variable declaration;

constant declaration;

 BEGIN

PL/SQL subprogram body;

 [EXCEPTION

 exception PL/SQl block;]

END;

Row Level Trigger-> a row level trigger is fired each time a row in the table affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If the triggering statement affects no rows, the trigger is not executed at all. Row level triggers are created by using FOR EACH ROW clause in the CREATE TRIGGER command.

Statement Level Trigger -> A statement trigger is fired once on behalf of the triggering statement, independent of the number of rows the triggering statement affects. A statement trigger fires even if no row affected. For example when an UPDATE command update 10 rows, the commands contained in the trigger executed only once and not for every processed row. Statement level triggers are the default types of triggers created by the CREATE TRIGGER command.

Advantages of Triggers:

• To enforce complex integrity constraints.

 • To prevent invalid transactions.

• To maintain replicate tables.

• To audit data modifications.

• To enforce complex security authorizations.

• To enforce complex business rules.