understanding triggers

Giganews Newsgroups
Subject: understanding triggers
Posted by:  Ted (r.ted.bye…@rogers.com)
Date: 7 Aug 2006

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

Replies