This discussion is archived
0 Replies Latest reply: Dec 15, 2010 10:55 AM by 150563 RSS

DML Events for DELETE not working

150563 Newbie
Currently Being Moderated
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 ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points