Update Trigger Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Trigger Question

Author

Topic

hateface
Starting Member

3 Posts

Posted - 2004-07-12 : 14:23:48

I am extremely new to Transact SQL and I'm trying to create an update trigger that will datestamp another colum using getdate(). I've created one and it's updating alright... Every row. I want it just to affect the record that is being updated. I'm sure that I'm just making some rookie mistake. I would appreciate any help on this. Thanks.
Here is my trigger:

CREATE TRIGGER DATESTAMP
ON dbo.testtable
FOR UPDATE AS
IF UPDATE (acctcreated)
BEGIN
UPDATE testtable
SET createddate = GETDATE()
END

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-12 : 15:17:51

YOu need to join to the inserted table based on a key....

Look up inserted in BOL

Brett

8-)

hateface
Starting Member

3 Posts

Posted - 2004-07-12 : 15:38:35

Found it. Thanks...
CREATE TRIGGER DATESTAMP
ON dbo.testtable
FOR UPDATE AS
IF UPDATE (acctcreated)
BEGIN
UPDATE testtable
SET createddate = GETDATE()
FROM testtable join inserted i
on testtable.id = i.id
END

Kristen
Test

22859 Posts

Posted - 2004-07-13 : 01:58:27

Hmmm ... when does acctcreated get set? Sound from the name (and the "createdate" column name) like this is done when the record is initially inserted, but maybe I've got that wrong?

I'm curious as to what the Business Meaning of "IF UPDATE (acctcreated)" is - what does that column hold and under what circumstances could it be modified?

And could it be part of an UPDATE statement even though it was not modified?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources