If the email_address is updated in the nav.temp1, then I would like to insert the record in the nav.temp1_hist with the old email_address and update user fields before updating the email_address in the nav.temp1. I am using the following trigger to do this task
create trigger nav.trg_update_temp1_hist no cascade before update of email_address on nav.temp1
REFERENCING OLD AS old_row FOR EACH ROW MODE DB2SQL
insert into nav.temp1_hist (user_id, type, email_address, update_user, update_date)
values (old_row.user_id, 'EMAIL', old_row.email_address, old_row.update_user, current timestamp);
But I am getting the following error message when creating the trigger.
The trigger "nav.trg_update_temp1_hist" is defined with an unsupported triggered SQL statement.. SQLCODE=-797, SQLSTATE=42987, DRIVER=3.63.108
The TRIGGER is how you tell the world that you cannot write declarative code and have to lapse back to 1950's COBOL or Auto_Coder to do your job.
And not posting DDL is how you express contempt for Netiquette. The useless punch card file skeleton seems to include a “blood_type” column; anytime I see a generic ”<nothing in particular>_type” column in a table I assume it is blood. Hey, why not? That follows ISO-11179 standards and you do not.
Why did you violate the data versus meta-data rules of data modeling? Why don't you know that rows are not records? That is fundamental to RDBMS!
To track the history of, say, Foobars we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a foobar had a particular value. Here is the skeleton.
CREATE TABLE Foobar_History
(foo_id CHAR(9) NOT NULL,
start_date DATE NOT NULL,
end_date DATETIME, --null means current
CHECK (start_date <= end_date),
foo_status INTEGER NOT NULL,
PRIMARY KEY (foo_id, start_date));
When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;
AND COALESCE (end_date, CURRENT_TIMESTAMP);
There are more tricks in the DDL to prevent gaps, etc with deferred constraints.