|
|
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
Navigation:
[Reply to this message]
|