Posted by Ted on 08/07/06 17:18

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

• Next in forum: Re: Understanding constraints and binding
• Prev in forum: Re: trouble porting a trivially simple function - with declared variables
• Thread view: understanding triggers

[Reply to this message]