0 Replies Latest reply: Dec 15, 2010 12:55 PM by 150563 RSS

    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 ?