TRIGGERS

TRIGGERS: 
                      A Set of commands that are triggered to execute when an event occurs in the data base. 

  • triggers stored in USER_TRIGGERS system table.
  • they are used to impose business rules or user defined restrictions on table columns.
syntax:
            CREATE [ OR REPLACE ] TRIGGER  Trigger_name
            { BEFORE / AFTER /  INSTEAD OF }
              { INSERT [ OR ] UPDATE [ OR ] DELETE }
             [ OF <column_name> ] ON <table_name>
             [REFERENCING :OLD AS o :NEW AS n ]
             [ FOR EACH ROW ]
             [ FOLLOWS another_trigger ]
             [ ENABLE / DISABLE ]
             [ WHEN condition ]
             DECLARE
                   declare statements;
             BEGIN
                    executable statements;
             EXCEPTION
                     exception handling statements
              END;

 Explanation About syntax:-

  • CREATE [OR REPLACE] TRIGGER <trigger_name> – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
  • {BEFORE | AFTER | INSTEAD OF } – This clause indicates at what time the trigger should get fired. i.e. for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. Before and after cannot be used to create a trigger on a view.
  • {INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
  • [OF column_name] – This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
  • [ON table_name] – This clause identifies the name of the table or view to which the trigger is associated.
  • [REFERENCING OLD AS o NEW AS n] – This clause is used to reference the old and new values of the data being changed. By default, you reference the values as: old.column_name or: new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
  • [FOR EACH ROW] – This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e. statement level Trigger).
The FOLLOWS option allows you to specify the order in which triggers should fire. This applies to triggers that are defined on the same table and that fire at the same timing point.Note that the trigger referenced in the FOLLOWS clause must already exist and have been successfully compiled.
The ENABLE and DISABLE clauses specify whether the trigger is created in the enabled or disabled state. When the trigger is enabled, it fires when a triggering event occurs. Similarly, when a trigger is disabled, it does not fire when a triggering event occurs. Note that when trigger is first created without an ENABLE or DISABLE clause, it is enabled by default.
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;
  • WHEN (condition) – This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

Triggers are used for different purposes:
  •  Enforcing complex business rules that cannot be defined by using integrity      Constraints.
  •  Maintaining complex security rules.
  •  Automatically generating values for derived columns.
  •  Collecting statistical information on table accesses.
  •  Preventing invalid transactions.
  •  Providing value auditing.
TRIGGER RESTRICTIONS:
  •    A trigger may not issue a transactional control statement such as COMMIT, SAVEPOINT, or ROLLBACK. When the trigger fires, all operations performed become part of a transaction.When this transaction is committed or rolled back, the operations performed by the trigger are committed or rolled back as well. An exception to this rule is a trigger that contains an autonomous transaction. Autonomous transactions are discussed in detail later in this lab.
  • Any function or procedure called by a trigger may not issue a transactional control statement unless it contains an autonomous transaction.
  •  It is not permissible to declare LONG or LONG RAW variables in the body of a trigger.

              

Comments

Post a Comment

Popular posts from this blog

PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE : We want to maintain state of the global variable we are using pragma serially_reusable in packages. ------------with out serially reusable pragma in packages SQL> create or replace package pkg3            is                   g number(3):=27;            end pkg3;           / Package created. SQL> begin                pkg3.g :=30;           end;          / PL/SQL procedure successfully completed. SQL> begin                  dbms_output.put_line(pkg3.g);           end;          / 30 PL/SQL procedure successfully completed. -------------------------------------------- serially reusable in packages SQL> create...
Read more

COLLECTION METHODS

Collection methods: A collection method is a built-in function or procedure that operates on collections and is called using dot notation. Following are the functions used with collections. EXISTS COUNT LIMIT FIRST AND LAST PRIOR AND NEXT EXTEND TRIM DELETE Syntax: collection_name.method_name [(parameters)] Collection methods cannot be called from SQL statements. Only the EXISTS method can be used on a NULL collection. All other methods applied on a null collection raise the COLLECTION_IS_NULL error. 1.COUNT: COUNT method returns the number of elements with the space allocated in Varrays and Nested tables. The COUNT method displays number of elements in associate arrays. COUNT can be smaller than LIMIT for Varrays. Example: DECLARE --Index by table type    TYPE etab IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;    emptab  etab; --Nested Table Type    TYPE tab_type IS TABLE OF VARCHAR2 (10);    t...
Read more

CURSOR PARAMETERS

CURSOR WITH  PARAMETERS: A cursor can be declare with parameters. Note: cursor parameters can be assigned default values. the scope of the cursor  parameters is local to the cursor the mode of the  parameters can only be IN. Example: Declare   Cursor c1 (p_deptno  emp.deptno%type) is   select ename,sal,deptno   from emp   where deptno=p_deptno; begin     for i in c1 (10) loop     dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);     end loop;         for i in c1(20) loop         dbms_output.put_line(i.ename||' '||i.sal||'  '||i.deptno);         end loop;     dbms_output.put_line('hai'); end; /
Read more