Hello. I'm having some trouble with a trigger. I have a load of tables which have the fields:
RecordId - NUMBER - Record Identifier
Created - TIMESTAMP DEFAULT CURRENT_TIMESTAMP - Date of creation
CreatedBy - VARCHAR2(6) - User that created record
LastUpdated - TIMESTAMP DEFAULT CURRENT_TIMESTAMP - Date of last update
LastUpdatedBy - VARCHAR2(6) - Last user to update
I've created a trigger to automatically fill in these values when an INSERT statement is executed but the values are not informed. The problem is with the UPDATE statements. I want the trigger to automatically set LastUpdatedBy to a default value and LastUpdated to the CURRENT_TIMESTAMP when these values are not set in the UPDATE statement. The code I had was:
Code:
CREATE OR REPLACE TRIGGER {TRIGGER NAME}
BEFORE INSERT OR UPDATE ON {TABLE NAME}
REFERENCING NEW AS n OLD AS o
FOR EACH ROW
BEGIN
IF INSERTING THEN
IF (:n.RecordId IS NULL) THEN
SELECT {SEQUENCE NAME}.nextVal INTO :n.RecordId FROM dual;
END IF;
IF (:n.CreatedBy IS NULL) THEN
:n.CreatedBy := '{NAME}';
END IF;
IF (:n.LastUpdatedBy IS NULL) THEN
:n.LastUpdatedBy := '{NAME}';
END IF;
END IF;
IF UPDATING THEN
IF (:n.LastUpdatedBy IS NULL) THEN
:n.LastUpdatedBy := '{NAME}';
END IF;
IF (:n.LastUpdated IS NULL) THEN
:n.LastUpdated := CURRENT_TIMESTAMP;
END IF;
END IF;
END;
The problem is, of course, NEW references what the record will look like after the UPDATE statement, so LastUpdatedBy and LastUpdated are never updated because they were set on the INSERT statement.
Is there any way to find out if the triggering statement is setting these fields? Or any way to read the triggering statement from within the trigger?
I'm open to suggestions. Thanks.
Dev Shed Forums Sponsor:
March 23rd, 2005, 04:16 AM
Problems with trigger
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
