Trigger

 Introduction to Oracle Statement-level triggers

A statement-level trigger is fired whenever a trigger event occurs on a table regardless of how many rows are affected. 

In other words, a statement-level trigger executes once for each transaction.

For example, if you update 1000 rows in a table, then a statement-level trigger on that table would only be executed once.

even it will execute without no rows affected

Due to its features, a statement-level trigger is not often used for data-related activities like auditing the changes 

of the data in the associated table. It’s typically used to enforce extra security measures on the kind of transaction that may 

be performed on a table.

Row-level triggers fires once for each row affected by the triggering event such as INSERT, UPDATE, or DELETE.

Row-level triggers are useful for data-related activities such as data auditing and data validation.

Pragma Autonomous_transaction;

------------------------------

when we issue rollback or commit inside the trigger it throw error

create or replace trigger mytriggers 

after insert on mytable

--declare

--pragma autonomous_transaction;

--null;

begin

  insert into chidam values('fire');

  commit;

end;

execute : insert into mytable values(1,'chidam1','y1');

it throws error we need to avoid for this used

create or replace trigger mytriggers 

after insert on mytable

declare

pragma autonomous_transaction;

--null;

begin

  insert into chidam values('fire');

  commit;

end;

=======================================================

Mutation

in row level trigger , trigger is written on one table , trying to use the same table in execution part it will throw error

avoid : convert the row level trigger to statement level trigger

8

SQL Error: ORA-04091: table APPS.MYTABLE is mutating, trigger/function may not see it

Avoid : use statement

=====================================

create or replace trigger mytriggers 

after insert on mytable

for each row

declare

lv_number number;

begin

  --insert into chidam values('fire');

  --select count(*) into lv_number from mytable where status='y';

  --dbms_output.put_line(lv_number);

  insert_data;

 -- commit;

end;

*Action:   Ensure that before returning from an autonomous PL/SQL block,

           any active autonomous transactions are explicitly committed

           or rolled bac

create or replace procedure insert_data

is

pragma autonomous_transaction;

begin

insert into chidam values('Akshara1');

commit; --- we should use commit or rollback 

end;    

================

calling function 

----------------

create or replace trigger mytriggers 

after insert on mytable

for each row

declare

lv_number number;

begin

  insert into chidam values('fire');

  --select count(*) into lv_number from mytable where status='y';

  --dbms_output.put_line(lv_number);

  insert_data;

  dbms_output.put_line(insert_data_fun);

 -- commit;

end;

---------------------------------------------

9

A mutating table occurs when a statement causes a trigger to fire and that trigger references the table that caused the trigger. The best way to avoid such problems is to not use triggers, but I suspect the DBA didn’t take the time to do that. He could have done one of the following:

Changed the trigger to an after trigger.

Changed it from a row level trigger to a statement level trigger.

Convert to a Compound Trigger.

Modified the structure of the triggers to use a combination of row and statement level triggers.

Made the trigger autonomous with a commit in it.

----------------------