[SOLVED] Will after statement level trigger work on dml operations

Will after statement level trigger work on dml operations

I have created this statement level trigger but it executes like row level trigger...

CREATE OR REPLACE TRIGGER trg_citizens_log
AFTER INSERT ON citizens
BEGIN
   INSERT INTO trigger_log ( log_id,trigger_name, log_time)
                   VALUES ( trigger_log_SEQ.nextval,'STATEMENT_TRIGGER', SYSTIMESTAMP );
END;
/

I have checked with these insert script..

BEGIN
  INSERT INTO CITIZENS VALUES (2, 'JOSH', 'MARK', 40);
  INSERT INTO CITIZENS VALUES (3, 'MANI', 'RAJ', 38);
  INSERT INTO CITIZENS VALUES (4, 'VIGNESH', 'KUMAR', 34);       
END;
/

INSERT ALL
  INTO CITIZENS VALUES (2, 'JOSH', 'MARK', 40)
  INTO CITIZENS VALUES (3, 'MANI', 'RAJ', 38)
  INTO CITIZENS VALUES (4, 'VIGNESH', 'KUMAR', 34)
SELECT * FROM dual;

Can anyone explain me why its behaving like this?

Solution

You are executing multiple SQL statements. In your first example, you have three separate SQL statements executed one after the other (the ; after each line is a statement terminator - which means you have three statements that get executed by your one PL/SQL exec call). That'll fire that statement level trigger three times.

In the second example, you are using INSERT ALL which under the covers gets transformed into separate statements (since the rows might have to go to different tables - they cannot be regarded as a single statement affecting one target).

So, to test a statement level trigger vs. a row-level trigger, use an array insert (INSERT SELECT) that is truly one statement that loads multiple rows:

INSERT INTO citizens SELECT id,firstname,lastname,age FROM datasource

Mock-up test:

INSERT INTO citizens SELECT id,firstname,lastname,age FROM (
(SELECT 2 id 'JOSH' firstname, 'MARK' lastname, 40 age FROM dual UNION ALL
 SELECT 3, 'MANI', 'RAJ', 38 FROM dual UNION ALL
 SELECT 4, 'VIGNESH', 'KUMAR', 34 FROM dual);