Hi


I know row level triggers are used for maintaining history files. In application development where do we use statement level triggers? Can anyone give one good example for it?

Regards
Babi

Statement-level trigger fires once per triggering statement regardless of the number of rows affected by the triggering event. Create table employeeaudit ( dt date, usr varchar2(20), action varchar2(50) ); create or replace trigger employee_audit after insert or update or delete on emp declare action varchar2(50); begin if inserting then action := 'new emp record is add'; elsif updating then action := 'emp record is updated'; elsif deleting then action := 'emp record is deleted'; end if; insert into employeeaudit values (sysdate, user,action); end; drop table employeeaudit;

Statement-level trigger fires once per triggering statement regardless of the number of rows affected by the triggering event.

Create table employeeaudit ( dt date,
usr varchar2(20),
action varchar2(50) );


create or replace trigger employee_audit
after insert or update or delete on emp
declare action varchar2(50);
begin
if inserting then action := 'new emp record is add';
elsif updating then action := 'emp record is updated';
elsif deleting then action := 'emp record is deleted';
end if;
insert into employeeaudit values (sysdate, user,action);
end;

drop table employeeaudit

Create table entity_x
( a varchar2(20), b number(10), c date);
here we are creating a trigger;
create or replace trigger statement_level
before update on entity_x
declare vmessage varchar2(30) := 'statement level trigger is fired';
begin
dbms_output.put_line(vmessage);
end statement_level;
/
set serveroutput on
insert into entity_x (a) values ('name1');
update entity_x set a = 'name2';

Imp Note: Here we are having before and after in triggers, means we are giving instruction to that when it should perform the action, In detail: Before means before compiling the statement the trigger will be fired, After means After the compilation The trigger will be fired

when you are executing this particular statement, the pl/sql compiler will detect that it is having update condition so it will check whether any trigger is there for update command for this particular entity, so here we are having a trigger related with it, so it will show you the message what you had given there, here i gave the message 'statement level trigger is fired'. You can use not only (dbms_output.put_line), also you can use anonymous blocks or you can call procedures to perform an action what ever you want at that place, here the anonymous blocks are more affective.
'susarlasireesha' also given a good example, u can try what i gave for you, after that u try the another, what she had given is a multitasking trigger. So u can see the difference.