40. TRIGGERS

Triggers:-

·         They are the stored programs fires implicitly unlike subprograms.

·         Triggers fires automatically when event occurs.

·         We can’t call trigger and pass parameters and can’t nest (trigger with in trigger).

Usages:-

·         Data auditing

·         Enforcing the referential integrity

·         Security

·         Data replication

·         Application monitoring(AM)

·         Enforcing the data

·         Triggers have their own name space.

·         Triggers won’t allow commit and rollback, but by using “pragma atonomous transaction we can mention commit and rollback in triggers.

·         Basically triggers are of two types

1. Application trigger

2. Database triggers

Database Triggers:-

       1.   DML triggers     (table)

       2.   Instead of trigger    (views)

       3.   DDL trigger   (schema)       // 3 and 4 never write these in DBA level.

       4.   DB trigger     (database)

       5.   Compound trigger   (11g)(table)

We will write triggers on tables, views, schema and database.

DML Triggers:-

·         We provide DML triggere on table

·         Based on DML events execution of trigger takes place implicitly.

·         This triggers are again of two types:

         1.  Statement level trigger (or) table level trigger

         2. Row level trigger

Statement Level Trigger:-

This trigger executes only once for an entire table/for bunch of records execution of this trigger takes place atleast once. Even if table data won’t get effected.

Row level Trigger:-

·         this trigger executes for each of row.

·         This trigger won’t get executes atleast once if table data won’t get effected.

Syntax of DML trigger:-

Create or replace trigger (1)before/after DML operations on [disable/enable](2)[foreach row](3)[when condition][declare]

Begin

Code;

Exception

End;

(1)-Trigger Timing:-

It specifies when trigger has to fire.

(2)-Trigger Level:-

Specifies what ever row level/statement level.if you mention for row i.e row level else statement level but by default statement level.

(3)-Trigger Condition:-(Not Preferrable)

If further provides the additional restriction on trigger execution.in trigger conditon should always be a boolean expression statement level trigger won’t allow trigger condition.

(4)-Trigger Body:-

It specifies what action we have to perform through trigger.

Ex for Statement Level Trigger:-

Write a statement level trigger so not tto allow any DML operation on a table on weekends.

Create table stab(sno number(5))

Create or replace trigger string before insert or update or delete on stab;

Begin

If to-char(sysdate,’dy’)in(‘thu’,’sun’)then raise_application_error

(-20003,’DML operations are not allowed on weekends’);

Else

DOPL(‘dml operations’);

End if;

End string;

·         Trigger created.

·         Insert into stab values(10);    //invalid

·         Select * from stab;

          No rows selected.

·         Generally most of the triggers make use of ‘before’ instead of after. We can define a trigger name with same table name on which we are defining

·         Create or replace trigger stab on stab.

Note: Because trigger uses different namespaces, table uses different namespaces so possible.

Row level Trigger:

It will make use of pseudo records as shown in following table.

Virtual table:

	old	new
insert update delete	null     ü    ü	ü    ü                   null

Here we will represent new and old in this program i.e row level trigger program.

Write a row level trigger to update the salaries witch one specific value for an increment, if Any one of the existing salaries is greater than new salaries then I have to cancel all the transactions happened by trigger.

Create or replace trigger ustrig before update on emp1;

For each row

Begin

If :old.sal>:new.sal then

Raise_application_error(-20003,’sal cannot be decremented’);

Else

DOPL(‘icremented’);end if;

End;

·         From the trigger if we get any error, all the previous transactions will get cancelled.

Eg: For Trigger when Condition:-

·         When condition skips the execution of trigger body when ‘when condition’ becomes ‘false’.(but insertion takes place).

·         If when condition becomes true then allows trigger body to execute.

·         Create or replace trigger log trig before insert or update or delete on log tab;

For each row

when(to_char(sysdate,’HH’)not between ‘05’ and ‘08’)

begin

DOPL(‘login time’);

End;

Insert into logtab values(10);

1 row created.

Create table logtab(sno number(5));

Using: New and : Old in when Condition:-

Create or replace trigger emp1 trig before insert or update or delete on emp1;

For each row

When(new.sal

Begin

DOPL(‘oldsal

End;

·         In when condition we won’t use :new and :old but in trigger body we can use, if you use also it throws an error.

·         Update emp1 set sal=4000;

Note: Trigger when condition won’t allows colons to refer old and new values.

Enforcing Referential Integrity:-

·         Triggers can’t chek the old/existing data, it checks only incoming data but where as in the case of constraints they check both incoming and existing data.

·         Constraints can give guarantee for centralized data.

·         Triggers are an advance of constraints.

·         Create trigger contrig after insert on ltab;

For each row

Begin

Insert into ptab values(:new.sno);

End;

·         Insert into ltab values(11);

·         Before of constraint checking trigger execution takes place.

Order of Execution:

·         Before statement level trigger(BSLT).

·         Before row level trigger(BRLT)>

·         After row level trigger(ARLT).

·         After statement level trigger(ASLT).

  

DDL Triggers:-

Syntax:

                    Create or replace trigger before/after DDL on schema

                    Begin

                    ……….

                    Exception

                    …………

                    End [trigger_name]

·         We write DDL on schema level(insert, update, delete).

DDL Attributes:_

In DDL we have more than 100 attributes but here we will go for

·         Ora_sysevent

·         Ora_DICT_obj_name

·         Ora_DICT_obj_type

We will provide DDL commands on schema, mainly for auditing purpose.

Create trigger audtrig before create or drop on schema

Begin

Insert into audtab values (user, ora_sysevent, ora_dict_obj_name, ora_dict_obj_type, sysdate);

End audtrig;

Create table audtab(suser varchar2(10), sevent varchar2(10), sname varchar2(10), stype varchar2(10), sdate date);

Create table SAM(sno number(5));

Select * from audtab;

Suser	Sevent	Sname	Stype	Sdate
Apps	Create	Sam	Table	11-mar-13

·         Drop table sam;

·         Select * from audtab;

We will write DB triggers on below mentioned:-

	Before Befor(T) Before(T) before		Log on Log off Shutdown Servererror Startup
					After(T) After After errornumber After(T)

 

·         Attaching the procedure to the trigger in the place of trigger body with ‘call statement’, so to reduce the redundancy as in the following eg.

Create or replace procedure p is

Begin

DOPL(‘hello’);

End;

Create table RAM(sno number(5));

Create or replace trigger protrig before insert on RAM

Call p(don’t use semicolon here, if you use you will get error)

/

-trigger created.

o   Insert into RAM values(10);

Hello

           -1 row created.

Advanced Topics In Triggers  Introduced From 11g:

·         It follows reserved word or keywords followed by 11g.

·         Oracle won’t give gurante for the order of trigger execution among the sametypes but, by using follows (11g) we can order the execution of triggers.

Create table RAM(sno number(5));

Create or replace trigger trig1 before insert on RAM

Begin

DOPL(‘trig1’);

End trig1;

            Create trigger trig2 before insert on RAM follows trig1

            Begin

DOPL(‘trig2’);

End trig2;

Output:-

Trig1

Trig2

Note:- If we did not use follows we get out put like

Trig2

Trig1

Oracle won’t wives guarantee for  order of execution, so from 11g on words we use ‘follows’.

Disable (11g):-

·         Prior to 11g we only have an option to disable an existing trigger from 11g on words we can disable a trigger while creating/defining.

·         Create or replace trigger disable before insert on RAM disable

Begin

DOPL(‘this is disable trigger’);

End disable;

·    Insert into RAM values(10);

-1 row created.

·    To enable/disable the trigger we’ve to make use of ALTER.

·    Alter trigger distrig enable;

·    Insert into RAM values(10);  //this is disable trigger.

-1 row created.

·    We can define max of 12 triggers on a table(DML).

We Can Write A Trigger To Fire When Updating On Specific Column:-

Create table uptab(sno number(5), loc varchar2(10));

Create trigger updating before updating of sno on uptab

Begin

DOPL(‘updating on column’);

End;

      ·         Insert into uptab values(10, ‘x’);

      ·         Updating utab set sno=20;                //updating on column

      ·         -1 row updating.

We Can Write Trigger On Multiple Columns:-

Create trigger uptrig1 before update of sno, loc on uptab

Begin

DOPL(‘updating on column’);

End;

Analyzing PL/SQL code:

       ·         Following things are useful to analyse the PL/SQL code so to increase the performance, easy identification, identifying the code flaws, so to protect from SQL injection.

       ·         User_object (to know the stored procedure valid/invalid)

       ·         User_source (to know the source code of stored procedures)

Note: Deterministicè from the function, for eg if you call one function for every calling if it returns same value in that situation if you write deterministic, for next execution it won’t executs, it acts as buffer.

·    User_objects:- Which is useful to verify the status of object i.e. whether valid or invalid.

·    User_source:- Which is useful to extract the source code of a stored procedure.

·    User_arguments:- Gives the information regarding arguments i.e name of argument, parameter_mode, datatype, scale, precision, position of arguments.

·    User_procedure:- Specifies whether the given program is deterministic, pipelined, parallel enabled, aggregate, result_cache, authid user….

·    User_identifires:- Gives the information regarding activities of identifiers, this table automatically get populated for a program (or) for session (or) for a database when we enable the PL/SQL tool (by default disabled) with an intialisation parameter called PL/scope-settings.

·    User_trigger:- Gives information regarding triggers.

·    User_dependences:- To find out reflection (or) information between referenced and dependent object we use this table.

Note: It won’t give information regarding remote dependenies for that we have to run a script file so to create one procedure and two tables. here deptree and ideptree are remote dependencies.

·    Dbms_metadata:- To create DDL report for an object. (dbms_utility.get_ddl(emp))

·    PL/scope tool(11g):- It is an utility, to get the information of identifier activites.

·    Eanblig PL/SQL tool:-

Syntax:-

Alter session set PLscpoe_setting=’identifiers:all’(enable);

Alter session(or system) set PLscpoe_setting=’identifiers:null’(disable);

Virtual Private Database (or) Fine Grained Access Control (FGAC):-

·    Let us take eg:

·    Select * from emp where 1=2;

Note:- In the above eg ‘where’ represents the condition should be added dynamically. And ‘1=2’ represents predicates.

·    Vpd is an alias of fine grained access control(FGAC), the basic concept of FGAC is to provide the row level security(RLS) (some applications deliberately needs this process).

·    Vpd provides security.

·    Vpd dynamically modify the SQL statement at run time by appending (or) attaching predicate through policy function.

·    Policy function is useful to return the predicate.

·    We will provide a package called along with schema and object names.

·    We can provide ‘N’ no of policy function on a single table and a single policy function can serve for multiple tables.

Definging Policy Function:-

Create or replace function pol_fun(p_schema varchar2,p_object varchar2)

Return varchar2 is l_pridicate varchar2(10);

Begin

L_proidicate:=’1=2’;

Return(l_pridicate);

End potfun;

Note: Here while creating policy functions(p_schema and p_object) these 2 in parameters are mandatory. ‘1=2’ this condition will append to select statement dynamically.

SQL/Plus:-

Var a varchar2(10);

Exec :a:=pot_fun(‘apps’,’emp’);

Print a;

Exec dbms_rls.add_policy(‘apps’,’emp1’,’res’,’appa’,’pol_fun’,select,insert,update’);

Select * from emp1;

Exec.dbms_rls.drop_policy(‘apps’,’emp1’,’res’);

Note:-  If you create next time with the very same name also same will be applicable.