Clarification: Question about triggers
This is a clarification about two divergent answers supplied by Dennis
Pimple and Jonathan Leffler in response to a question:
Dale Van Voorst (voorst@dordt.edu) asked:
>Two questions regarding triggers:
>1) [omitted since there was no problem with this answer]
>
>2) Since I currently have no book, can someone tell me if the following
>idea is applicable to the use of triggers.
>
>I want to keep track of the date of the last change to each row in a
>table. I'd like to avoid programming everything in 4gl just to make
>sure that the date always gets updated. It seems that triggers might
>be just the ticket. If I could automatically trigger an update of the
>date field whenever a row is updated, my life would be much easier.
>
>Is this a feasible use for a trigger?
Dennis said:
>>2) ...
>
>You can't update the triggering row with the trigger, so you can't
>update a column in the same row, I guess because of the infinite
>loop you would be conceivably be creating (since the update would
>trigger the action again, etc.). ...
Jonathan said:
>>2) ...
>
>Yes, it is feasible. ...
So, is it, or is it not, possible to automatically trigger an update of the
date field whenever a row is updated?
There are elements of truth to both answers, and the complete answer is
that it depends on how you write your UPDATE statements.
You cannot alter the data supplied by the user's UPDATE inside the trigger.
So, if you take the normal, lazy way of writing an update in I4GL using:
UPDATE SomeTable
SET * = somerecord.*
WHERE PKColumn = pkval
then you cannot write a trigger to alter the data placed in the table.
This means, in particular, that if there is an UpdTime or UpdUser column
in the record, then you cannot actually change that data using a trigger.
So, Dennis is correct!
However, if your UPDATE does not itself set the UpdTime and UpdUser columns,
then you can use a trigger to do the assignment automatically. For instance
(taking the code used in Jonathan's original answer):
CREATE TABLE 'wtsdba'.SecAction
(
ActionID CHAR(12) NOT NULL
PRIMARY KEY
CONSTRAINT 'wtsdba'.PK_SecAction,
Description VARCHAR(60) NOT NULL ,
UpdUser CHAR(8) DEFAULT USER NOT NULL,
UpdTime DATETIME YEAR TO SECOND
DEFAULT CURRENT YEAR TO SECOND NOT NULL
);
REVOKE ALL ON 'wtsdba'.SecAction FROM PUBLIC;
GRANT SELECT ON 'wtsdba'.SecAction TO PUBLIC AS 'wtsdba';
GRANT UPDATE(Description) ON 'wtsdba'.SecAction TO PUBLIC AS 'wtsdba';
CREATE TRIGGER 'wtsdba'.U2_SecAction UPDATE OF Description
ON 'wtsdba'.SecAction
REFERENCING NEW AS action
FOR EACH ROW
(UPDATE SecAction
SET UpdUser = USER,
UpdTime = CURRENT YEAR TO SECOND
WHERE ActionID = action.ActionID
);
Using a modified command interpreter which includes SLEEP as a built-in
command, and given this table, now run the following statements:
INSERT INTO SecAction (ActionID, Description) VALUES ('ABC', '123');
SELECT * FROM SecAction;SLEEP 2;
UPDATE SecAction SET Description = '234';
SELECT * FROM SecAction;
INSERT INTO SecAction (ActionID, Description) VALUES ('DEF', '123');
SELECT * FROM SecAction;SLEEP 2;
UPDATE SecAction SET Description = '234';
SELECT * FROM SecAction;
INSERT INTO SecAction (ActionID, Description) VALUES ('GHI', 'XXX');
SELECT * FROM SecAction;SLEEP 2;
UPDATE SecAction SET Description = 'Updated Action Description';
SELECT * FROM Secaction;
The last set of statements produced two lots of output:
ABC 234 johnl 1994-07-19 09:42:33
DEF 234 johnl 1994-07-19 09:42:33
GHI XXX johnl 1994-07-19 09:45:00
ABC Updated Action Description johnl 1994-07-19 09:45:02
DEF Updated Action Description johnl 1994-07-19 09:45:02
GHI Updated Action Description johnl 1994-07-19 09:45:02
So Jonathan is correct too!
The database has logging, and is using 5.02.UC1 OnLine on SunOS 4.1.3. As
can be seen, the INSERT generates the USER and CURRENT values from the
DEFAULT values specified in the CREATE TABLE statement, because those
columns are carefully excluded from the the actual INSERT. The UPDATE of
Description fires the trigger and changes the UpdUser and UpdTime columns.
To make this automatic, you should probably create a view on the SecAction
table, such as:
CREATE VIEW Action(ActionID, Description)
AS SELECT ActionID, Description FROM SecAction;
and all I4GL (or ISQL) modify operations should be done against this view
rather than the underlying base table. With careful use of permissions,
this could even be enforced fully by the permissions system.
Thus, the divergent answer can be reconciled. Both contain large elements
of truth, but overlook the features of the other. A fuller explanation
makes it clear.
Yours verbosely,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
= Message produced after discussion with Dennis Pimple
= (dennisp@informix.com), who didn't see this message in its
= present form before it was sent to comp.database.informix.
PS:
When a subsequent test was run on an empty table using just the first
INSERT and UPDATE statement, the output was:
ABC 123 johnl 1994-07-20 07:56:32
ABC 234 johnl 1994-07-20 07:56:34
Then doing the UPDATE and SELECT below produced the answer shown:
UPDATE SecAction SET UpdUser = 'wtsdba', UpdTime = CURRENT YEAR TO SECOND;
SELECT * FROM SecAction;
ABC 234 wtsdba 1994-07-20 07:57:08
There were no errors from the trigger, but the trigger update actions were
clearly over-ridden by the user-specified values for the updated row.