Results 1 to 4 of 4

Thread: Statement level triggers

  1. #1
    Contributing Member
    Join Date
    Mar 2008
    Answers
    66

    Statement level triggers

    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


  2. #2
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Statement level triggers

    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;


  3. #3
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Statement level triggers

    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


  4. #4
    Junior Member
    Join Date
    Feb 2008
    Answers
    15

    Re: Statement level triggers

    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.

    Last edited by su123; 03-03-2008 at 06:02 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact