Strange Behavior with Oracle 8 Triggers
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