472,974 Members | 1,735 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,974 software developers and data experts.

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 2733
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: raulgz | last post by:
I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks..
4
by: Mark Flippin | last post by:
I'm just starting to use triggers in my databases and find the support in Enterpise Manager lacking. Using Enterprise Manager and Query Analyzer you can maintain the triggers, but it's...
1
by: tim.pascoe | last post by:
I'm trying to generate scrips for a database, and everything so far has worked fine, except for the triggers. When I try and script existing triggers, all I get is a blank file - no SQL script. I...
4
by: stacdab | last post by:
We have a partitioned view with 4 underlying tables. The view and each of the underlying tables are in seperate databases on the same server. Inserts and deletes on the view work fine. We then...
5
by: Bruce | last post by:
I have several user defined functions which are referenced in triggers and views. For software upgrades, I need to be able to drop the triggers and views which reference these user defined...
0
by: Bruno Lavoie | last post by:
Hello, i'm etablishing a naming convention for a new project under postgresql. For tables, sequences, views, that's ok! I used good naming conventions for this in the past and i'll keep these...
0
debasisdas
by: debasisdas | last post by:
trigger sample code Ex#10 ======================= INSTEAD OF TRIGGER ---------------------------------------- create or replace trigger mytrig instead of delete or insert or update on eview...
0
debasisdas
by: debasisdas | last post by:
This thread contains some useful tips/sample codes regarding TRIGGERS in oracle, that the forum members may find useful. TRIGGERS: =============== Database trigger is a PL/SQL block that is...
4
by: --CELKO-- | last post by:
I need to convert a bunch of DB2 triggers to Oracle. Is there any kind of tools for this?
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.