0 Replies Latest reply on Dec 15, 2010 6:55 PM by 150563

    DML Events for DELETE not working

    150563
      I've tested new 11g feature for IUD DML Events. Insert and update worked fine, but not delete ...
      This is the test:

      drop table srv_tbl;
      create table srv_tbl(
      id_srv number(4) primary key,
      den_srv varchar2(100),
      prop_srv varchar2(25),
      obs_srv varchar2(100)
      );

      drop table srv_log;
      create table srv_log(
      logrec_id number(4) primary key,
      log_info varchar(1000),
      log_timestamp timestamp
      );
      DROP SEQUENCE LOG_SEQ_REC;
      /
      create sequence log_seq_rec
      INCREMENT BY 1 START WITH 1 MAXVALUE 10000 MINVALUE 1 ORDER;
      /
      create or replace trigger trg_ins_srv_log
      BEFORE INSERT ON SRV_LOG
      FOR EACH ROW
      begin
      :new.logrec_id := log_seq_rec.nextval;
      :new.log_timestamp := SYSTIMESTAMP;
      end;
      /
      --------------------------------------------------------------------------------

      -- Rule System Definition schema ----------------------------------------------
      -- Step 1 ----------------------------------------------------------------------
      -- UNDO
      BEGIN

      dbms_rlmgr.drop_rule_class (
      rule_class =>'ServiceEventClass'
      );


      dbms_rlmgr.drop_event_struct (
      event_struct => 'ServiceEvent'
      );

      end;
      /

      begin
      dbms_rlmgr.create_event_struct (
      event_struct => 'ServiceEvent'
      );

      dbms_rlmgr.add_elementary_attribute (
      event_struct => 'ServiceEvent',
      attr_name => 'SRV', --- Event name
      tab_alias => rlm$table_alias('rlshost.srv_tbl')
      );
      end;
      /
      --------------------------------------------------------------------------------
      -- Step 2 ----------------------------------------------------------------------
      -- UNDO
      begin
      dbms_rlmgr.drop_rule_class (
      rule_class =>'ServiceEventClass'
      );
      end;
      /

      BEGIN
      dbms_rlmgr.create_rule_class (
      rule_class =>'ServiceEventClass',
      event_struct =>'ServiceEvent',
      action_cbk =>'srv_rule_action',
      rlcls_prop => '<composite dmlevents="IUD" autocommit="NO" duration="TRANSACTION"/>',
      actprf_spec =>     'rule_creation_date DATE, rule_creation_by VARCHAR2(30), rule_action_call VARCHAR2(100)');
      END;
      /
      --------------------------------------------------------------------------------
      -- Step 3 ----------------------------------------------------------------------
      -- For Complex Event Single TABLE
      create or replace
      PROCEDURE "SRV_RULE_ACTION" (
      "SRV" ROWID,
      --"DMP" ROWID,
      RLM$RULE "SERVICEEVENTCLASS"%ROWTYPE)
      is
      BEGIN
      DBMS_OUTPUT.PUT_LINE('RULE ACTION PRECESSED!');
      EXECUTE IMMEDIATE
      'BEGIN ' || RLM$RULE.RULE_ACTION_CALL ||'; END;'
      USING SRV, rlm$rule.RULE_CREATION_BY;
      END;
      /
      CREATE OR REPLACE
      procedure SPEC_RULE_ACTION ( "SRV" ROWID,
      rule_creation_by VARCHAR2) is
      BEGIN
      INSERT INTO SRV_LOG(LOG_INFO) VALUES (
      'Rule Manager Job Done! - Composite DMLEvent:'
      || '[SPEC_RULE_CREATION_BY:' || SRV || '-' || ']');
      END;
      /
      --------------------------------------------------------------------------------
      -- Step 4: Add some rules ------------------------------------------------------
      SELECT * FROM SERVICEEVENTCLASS;
      DELETE FROM SERVICEEVENTCLASS;
      -- composite event rule with rule action SIMPLE condition
      insert into serviceeventclass (
      RLM$RULEID,
      rule_creation_date, RULE_CREATION_BY, rule_action_call,
      rlm$rulecond)
      values (
      1,
      sysdate,'RLSHOST', 'SPEC_RULE_ACTION(:1, :2)',
      '<condition>
      <object name="SRV">
      den_srv = ''TEST''
      </object>
      </condition>'
      );
      --
      insert into serviceeventclass (
      RLM$RULEID,
      rule_creation_date, RULE_CREATION_BY, rule_action_call,
      rlm$rulecond)
      values (
      1,
      sysdate,'RLSHOST', 'SPEC_RULE_ACTION(:1, :2)',
      '<condition>
      <object name="SRV">
      </object>
      </condition>'
      );
      COMMIT;
      --------------------------------------------------------------------------------
      -- Step 5: Make some DML Events -----------------------------------------------

      INSERT INTO SRV_TBL(ID_SRV, DEN_SRV) VALUES (1, 'TEST');
      INSERT INTO SRV_TBL(ID_SRV, DEN_SRV) VALUES (2, 'TEST');

      UPDATE SRV_TBL T SET T.DEN_SRV = 'TEST' WHERE T.ID_SRV = 1;
      delete from srv_tbl where den_srv = 'TEST';
      UPDATE SRV_TBL T SET T.DEN_SRV = 'TEST_';
      delete from srv_tbl where den_srv = 'TEST_';


      There is anyone that experimented something like this ?