Strange Behavior with Oracle 8 Triggers

From: Yi Zhao <yzhao11_at_yahoo.com>
Date: 12 Aug 2001 21:56:55 -0700
Message-ID: <ddbc4508.0108122056.344156c0_at_posting.google.com>


I have a trigger to record all changes to one table in EVENT and EVENT_DATA tables. Here is the strange thing. I put the trigger on two different servers. On one server, I did an INSERT and COMMIT. The record in EVENT table was still there and could be read through another database connection. But on the other server, I did an INSERT and saw an new row in the EVENT table. But after I did a COMMIT the record in EVENT table was gone.

Can any one give me a pointer on what kind of configuration work need to be done to make the second server right?

Here are the trigger and the schema.

CREATE OR REPLACE TRIGGER CUSTOMER_TABLE_insert_trigger AFTER insert ON CUSTOMER_TABLE FOR EACH ROW
DECLARE temp integer;
Begin
select EVENTID.NEXTVAL into temp from DUAL; insert into EVENT
(Table_Name, Trigger_Type, Time_Created, Event_ID) values ('CUSTOMER_TABLE', 'insert', sysdate, temp); Insert into EVENT_DATA ( Event_ID, Column_Name, Column_Type, String_Value )
values( temp, 'FIRSTNAME', 'VARCHAR', :new.FIRSTNAME);

Insert into EVENT_DATA ( Event_ID, Column_Name, Column_Type, String_Value )
values( temp, 'LASTNAME', 'VARCHAR', :new.LASTNAME); ...
end;

DROP TABLE EVENT;
CREATE TABLE EVENT(
   TABLE_NAME VARCHAR2(256),
   TRIGGER_TYPE VARCHAR2(32),
   TIME_CREATED DATE,
   EVENT_ID INTEGER ); DROP TABLE EVENT_DATA;
CREATE TABLE EVENT_DATA(
   EVENT_ID INTEGER,

   COLUMN_NAME VARCHAR2(256),
   COLUMN_TYPE VARCHAR2(32),
   STRING_VALUE VARCHAR2(256),

   IS_NEW INTEGER DEFAULT 0 ); CREATE OR REPLACE TRIGGER DELETE_ROW AFTER DELETE ON EVENT FOR EACH ROW
BEGIN
  DELETE EVENT_DATA WHERE EVENT_DATA.EVENT_ID = :OLD.EVENT_ID; END; Thanks,
  • YI
Received on Mon Aug 13 2001 - 06:56:55 CEST

Original text of this message