469,133 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

understanding triggers

Ted
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

Aug 7 '06 #1
3 2627
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
Aug 7 '06 #2
Ted

Hello Razvan Socol

Thanks for this. My error in using inserted is obvious after you
pointed it out.

What is the purpose of your "SET NOCOUNT ON"? Where is NOCOUNT defined
or declared?

Thanks again.

Ted

Aug 7 '06 #3
Hi, Ted

It is a best practice to issue "SET NOCOUNT ON" at the beginning of
triggers (and stored procedures) that perform any INSERT/UPDATE/DELETE
statements, in order to eliminate the additional "n row(s) affected"
message which would show up as the effect of those statements. If the
statement that causes the trigger is executed using ADO or OLE-DB, the
additional messages may mask a real error message, which would not be
raised by ADO as an error, until the corresponding resultset would be
retrieved using the .NextRecordset method.

See the chaper on "Client-side Error Handling" in ADO and ADO.Net from
the following article by Erland Sommarskog, SQL Server MVP:
http://www.sommarskog.se/error-handling-I.html

Here are some KB articles documenting similar problems:
http://support.microsoft.com/kb/q197528/
http://support.microsoft.com/default...b;en-us;195491
http://support.microsoft.com/kb/q240882/

Razvan

Ted wrote:
Hello Razvan Socol

Thanks for this. My error in using inserted is obvious after you
pointed it out.

What is the purpose of your "SET NOCOUNT ON"? Where is NOCOUNT defined
or declared?

Thanks again.

Ted
Aug 8 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by raulgz | last post: by
4 posts views Thread by Mark Flippin | last post: by
1 post views Thread by tim.pascoe | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
4 posts views Thread by --CELKO-- | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.