Statement Lvl Triggers, MERGE Stmt, INSERTING and UPDATING

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Statement Lvl Triggers, MERGE Stmt, INSERTING and UPDATING

Postby jcapzz » Tue Jan 14, 2014 10:27 pm

I did some testing with triggers a Merge statement today and it appears that in statement level triggers the INSERTING, UPDATING keywords do not work when executing the MERGE statement. Perhaps another reason NOT to use triggers for your very nicely written aricle on triggers.

Code: Select all
--------------------------------------------------------
--  DDL for Table TABLE1
--------------------------------------------------------

  CREATE TABLE "TABLE1"
   (   "MARKET" NUMBER(10,0),
   "XY" VARCHAR2(1 BYTE)
   )  ;
--------------------------------------------------------
--  Constraints for Table TABLE1
--------------------------------------------------------

  ALTER TABLE "GFKITSTAT"."TABLE1" MODIFY ("MARKET" NOT NULL ENABLE);
--------------------------------------------------------
--  DDL for Trigger TRIGGER_TABLE1_IUDAS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER TRIGGER_TABLE1_IUDAS
AFTER INSERT OR DELETE OR UPDATE ON TABLE1
Begin
  If Inserting Then
    Dbms_Output.Put_Line('After Statement Trigger: Inserting');
  ElsIf Updating Then
    Dbms_Output.Put_Line('After Statement Trigger: Updating');
  Else
    Dbms_Output.Put_Line('After Statement Trigger: Neither Inserting or Updating');
  end if;
END;
/
ALTER TRIGGER TRIGGER_TABLE1_IUDAS ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_TABLE1_IUDAR
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER TRIGGER_TABLE1_IUDAR
AFTER INSERT OR DELETE OR UPDATE ON TABLE1
FOR EACH ROW
BEGIN
  If Inserting Then
    Dbms_Output.Put_Line('After Row Statement Trigger: Inserting');
  ElsIf Updating Then
    Dbms_Output.Put_Line('After Row Trigger: Updating');
  Else
    Dbms_Output.Put_Line('After Row Trigger: Neither Inserting or Updating');
  End If;
END;
/
ALTER TRIGGER TRIGGER_TABLE1_IUDAR ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_TABLE1_IUDBR
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER TRIGGER_TABLE1_IUDBR"
BEFORE INSERT OR DELETE OR UPDATE ON TABLE1
FOR EACH ROW
BEGIN
  If Inserting Then
    Dbms_Output.Put_Line('Before Row Statement Trigger: Inserting');
  ElsIf Updating Then
    Dbms_Output.Put_Line('Before Row Trigger: Updating');
  Else
    Dbms_Output.Put_Line('Before Row Trigger: Neither Inserting or Updating');
  end if;
END;
/
ALTER TRIGGER TRIGGER_TABLE1_IUDBR" ENABLE;
--------------------------------------------------------
--  DDL for Trigger TRIGGER_TABLE1_IUDBS
--------------------------------------------------------

  CREATE OR REPLACE TRIGGER TRIGGER_TABLE1_IUDBS
BEFORE INSERT OR DELETE OR UPDATE ON TABLE1
BEGIN
  If Inserting Then
    Dbms_Output.Put_Line('Before Statement Statement Trigger: Inserting');
  ElsIf Updating Then
    Dbms_Output.Put_Line('Before Statement Trigger: Updating');
  Else
    Dbms_Output.Put_Line('Before Statement Trigger: Neither Inserting or Updating');
  end if;
END;
/
ALTER TRIGGER TRIGGER_TABLE1_IUDBS ENABLE;

REM INSERTING into EXPORT_TABLE
Insert into Table1 (MARKET,XY) values (1,'x');
Insert into Table1 (MARKET,XY) values (7,'x');
Insert into Table1 (MARKET,XY) values (6,'y');
Insert into Table1 (MARKET,XY) values (5,'x');
Insert into Table1 (MARKET,XY) values (4,'x');
Insert into Table1 (MARKET,XY) values (3,'y');
Insert into Table1 (MARKET,XY) values (2,'y');


INSERT Statement:

Code: Select all
insert into table1 values (8, 'y');
1 rows inserted.


DBMS Output:
Code: Select all
Before Statement Statement Trigger: Inserting
Before Row Statement Trigger: Inserting
After Row Statement Trigger: Inserting
After Statement Trigger: Inserting


Merge Statement:
Code: Select all
merge into table1
Using (
  Select Rownum r, chr(96 + rownum) AS xy
  From dual
  Connect By Rownum <= 10
  ) A ON (table1.Market = A.r)
  WHEN Matched then
    UPDATE SET XY = A.XY
  When Not Matched Then
  INSERT VALUES ( A.R, A.XY);

10 rows merged.


DBMS Output:
Code: Select all
Before Statement Trigger: Neither Inserting or Updating
Before Statement Trigger: Neither Inserting or Updating
Before Row Trigger: Updating
After Row Trigger: Updating
Before Row Trigger: Updating
After Row Trigger: Updating
Before Row Trigger: Updating
After Row Trigger: Updating
Before Row Trigger: Updating
After Row Trigger: Updating
Before Row Trigger: Updating
After Row Trigger: Updating
Before Row Trigger: Updating
After Row Trigger: Updating
Before Row Trigger: Updating
After Row Trigger: Updating
Before Row Statement Trigger: Inserting
After Row Statement Trigger: Inserting
Before Row Statement Trigger: Inserting
After Row Statement Trigger: Inserting
Before Row Statement Trigger: Inserting
After Row Statement Trigger: Inserting
After Statement Trigger: Neither Inserting or Updating
After Statement Trigger: Neither Inserting or Updating

SAMPLE INSERT:
jcapzz
Member
 
Posts: 1
Joined: Tue Jan 14, 2014 10:03 pm

Re: Statement Lvl Triggers, MERGE Stmt, INSERTING and UPDATI

Postby Tim... » Wed Jan 15, 2014 9:23 am

Hi.

Well, that is appropriate really, because a merge is neither an insert or an update really. For example, if the merge finds nothing to update, only inserts, it would have been wrong to fire the before statement in the context of inserting, and vice versa. Remember, the before statement, is exactly that. Before the statement. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest

cron