Posted by Razvan Socol on 08/07/06 17:40

Hello, Ted

The last UPDATE in your trigger will not affect the original table (you
will get the following error: "The logical tables INSERTED and DELETED
cannot be updated.").

I would use a trigger like this:

CREATE TRIGGER update_history ON test FOR UPDATE
AS
IF @@ROWCOUNT>0
BEGIN
SET NOCOUNT ON
INSERT INTO test_history (an_ndx, a_var, last_edit_timestamp)
SELECT an_ndx, a_var, last_edit_timestamp FROM deleted;
UPDATE test SET last_edit_timestamp = CURRENT_TIMESTAMP
WHERE an_ndx IN (SELECT an_ndx FROM inserted)
END;

The above trigger works on the assumption that an_ndx will never be
changed (because it is an IDENTITY column) and that it uniquely
identifies a row (because it is a PRIMARY KEY).

Razvan

Ted wrote:
> Please consider the following example.
>
> CREATE TABLE test (
> an_ndx int NOT NULL primary key identity(1,1),
> a_var varchar(48) NOT NULL,
> last_edit_timestamp datetime NOT NULL default CURRENT_TIMESTAMP
> );
>
> CREATE TABLE test_history (
> an_ndx int NOT NULL,
> a_var varchar(48) NOT NULL,
> last_edit_timestamp datetime NOT NULL,
> current_edit_timestamp datetime NOT NULL default CURRENT_TIMESTAMP
> );
>
> GO
>
> CREATE TRIGGER update_history ON test FOR UPDATE
> AS
> BEGIN
> INSERT INTO test_history (an_ndx, a_var, last_edit_timestamp)
> SELECT * FROM deleted;
> UPDATE inserted SET last_edit_timestamp = CURRENT_TIMESTAMP;
> END;
>
> The question is, does this do what I think it should do? What I
> intended: An insert into test results in default values for an_ndx and
> last_edit_timestamp. An update to test results in the original row(s)
> being copied to test_history, with a default value for
> current_edit_timestamp, and the value of last_edit_timestamp being
> updated to the current timestamp. Each record in test_history should
> have the valid time interval (last_edit_timestamp to
> current_edit_timestamp) for each value a_var has had for the "object"
> or "record" identified by an_ndx.
>
> If not, what change(s) are needed to make it do what I want it to do?
>
> Will the trigger I defined above behave properly (i.e. as I intended)
> if more than one record needs to be updated?
>
> Thanks
>
> Ted

[Back to original message]