locking question when using before update trigger

I'm trying to understand a locking issue I'm having with my database.

Scenario is as follows:

1. I have a before update trigger on all of my tables to update a timestamp column (so I know the last time a row was updated). This column is never updated directly by any SQL. Trigger looks like the following:

create or replace trigger im_ins_upd_table_a BEFORE INSERT OR UPDATE ON table_a
FOR EACH ROW
BEGIN
  if not ((:new.replicated_flag = 'Y') and (:old.replicated_flag = 'N')) THEN
    :new.last_touch_date := sysdate;
  END IF;
END; 2. I have 2 tables, 1 is named table_a and the other table_b. table_b has a foreign key to table_a. I have made sure that ALL foreign key columns in my schema are indexed.

When I do an update on table_a (any column other than last_touch_date) any future inserts into table_b are blocked until I commit/rollback the update on table_a (even though I haven't even updated the column that was the foreign key from table_b).

If I disable the trigger I have no blocking issues.

Any help would be greatly appreciated.

Tim Callaghan Received on Tue Jan 28 2003 - 14:29:57 CST