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_times tamp datetime NOT NULL default CURRENT_TIMESTA MP
);

CREATE TABLE test_history (
an_ndx int NOT NULL,
a_var varchar(48) NOT NULL,
last_edit_times tamp datetime NOT NULL,
current_edit_ti mestamp datetime NOT NULL default CURRENT_TIMESTA MP
);

GO

CREATE TRIGGER update_history ON test FOR UPDATE
AS
BEGIN
INSERT INTO test_history (an_ndx, a_var, last_edit_times tamp)
SELECT * FROM deleted;
UPDATE inserted SET last_edit_times tamp = CURRENT_TIMESTA MP;
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_times tamp. An update to test results in the original row(s)
being copied to test_history, with a default value for
current_edit_ti mestamp, and the value of last_edit_times tamp being
updated to the current timestamp. Each record in test_history should
have the valid time interval (last_edit_time stamp to
current_edit_ti mestamp) 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

[Razvan Socol]

Re: understanding triggers

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_times tamp)
SELECT an_ndx, a_var, last_edit_times tamp FROM deleted;
UPDATE test SET last_edit_times tamp = CURRENT_TIMESTA MP
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_times tamp datetime NOT NULL default CURRENT_TIMESTA MP
);
>
CREATE TABLE test_history (
an_ndx int NOT NULL,
a_var varchar(48) NOT NULL,
last_edit_times tamp datetime NOT NULL,
current_edit_ti mestamp datetime NOT NULL default CURRENT_TIMESTA MP
);
>
GO
>
CREATE TRIGGER update_history ON test FOR UPDATE
AS
BEGIN
INSERT INTO test_history (an_ndx, a_var, last_edit_times tamp)
SELECT * FROM deleted;
UPDATE inserted SET last_edit_times tamp = CURRENT_TIMESTA MP;
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_times tamp. An update to test results in the original row(s)
being copied to test_history, with a default value for
current_edit_ti mestamp, and the value of last_edit_times tamp being
updated to the current timestamp. Each record in test_history should
have the valid time interval (last_edit_time stamp to
current_edit_ti mestamp) 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

[Ted]

Re: understanding triggers


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

[Razvan Socol]

Re: understanding triggers

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.aspx?scid=kb;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