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