This forum is currently locked. You can't register or post questions at this time. (read more)

Statement Lvl Triggers, MERGE Stmt, INSERTING and UPDATING

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

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:

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron