Subject Pessimistic locking and Triggers
Author gjuncu@compas.dntcj.ro
Hello list!

I am having the following problem (I'm using IBO 3.6Cf):

I have the following table:

CREATE TABLE DOC_HDR (
ID UNIQUE_ID NOT NULL,
DOC_NO INTEGER,
...
DATA_C TIMESTAMP,
DATA_M TIMESTAMP

where ID is primary key, and two triggers:

CREATE TRIGGER DOC_HDR_TIMESTAMP_BI FOR DOC_HDR
ACTIVE BEFORE INSERT POSITION 0
as
begin
NEW.DATA_C = CURRENT_TIMESTAMP;
end

CREATE TRIGGER DOC_HDR_TIMESTAMP_BU FOR DOC_HDR
ACTIVE BEFORE UPDATE POSITION 0
as
begin
NEW.DATA_M = CURRENT_TIMESTAMP;
end

To access this table, I am using a TIB_Query with
PessimisticLocking := TRUE and
LockSQL := 'UPDATE DOC_HDR SET DOC_NO = DOC_NO WHERE ID = :OLD_ID;

The problem is that when I insert a new record, I want than DATA_M to
remain null (this indicates that the record was not modified since it
was created), but because of Pessimistic Locking, the DATA_M field is
set 2 seconds after DATA_C when inserting data. Is there a way to
avoid firing Update triggers (and so to maintain DATA_M to null) when
doing inserts with PessimisticLocking activated?

Thanks!

Gabriel