In my table I have a column that is MODIFY_DATE.  I'm trying to track when
the row was last updated.  The default value on the column is getdate() so I
do know when the row was inserted.

I wrote a trigger like this:

CREATE TRIGGER [MODIFY_DATE] ON [dbo].[cp_Exceptions]
FOR UPDATE
AS
UPDATE cp_Exceptions
SET modify_date = getdate()

but that updates the modification time of every row.  Not just the row that
was updated.  I can't for the life of me figure out what I'm supposed to do.

Steve

On Thu, 18 Aug 2005 14:47:25 -0700, - Steve - wrote:

Show quoteHide quoteHi Steve,

CREATE TRIGGER [MODIFY_DATE] ON [dbo].[cp_Exceptions]
FOR UPDATE
AS
UPDATE cp_Exceptions
SET modify_date = getdate()
WHERE EXISTS (SELECT *
              FROM   inserted
              WHERE  inserted.PKcolumn = cp_Exceptions.PKcolumn)

Replace "PKcolumn" with your primary key column (and add extra AND ...
lines if your primary key is composite).

Best, Hugo

update cp_Exceptions
set modifiy_date = getdate()
join inserted on inserted.PK = cp_Exceptions.PK

Show quoteHide quote

Todd Beaulieu...

Are you the same Todd who used to code DataFlex and hang out on the
CompuServe forum back in the late eighties/early ninties?

If you are, email me: jeff at eightup, that's with a com.

-j



*** Sent via Developersdex http://www.developersdex.com ***

The trigger responds to an UPDATE against the table.  Whether the update
affects 1 row, 20 rows, or all rows.

Your trigger is not identifying which rows to update; it is just like going
to query analyzer and running:

UPDATE cp_Exceptions SET modify_date = getdate()

This is going to affect all rows, because there is no WHERE clause!  What
happens when there is no WHERE clause?

So, you need to identify the primary key of your table, so we can tell you
exactly how to code the UPDATE statement.  Generally, it will be something
like this:

UPDATE cp_Exceptions
    SET modify_date = CURRENT_TIMESTAMP
    WHERE PK_Column IN
    (
        SELECT PK_Column FROM inserted
    )

You can also use EXISTS:

UPDATE cp_Exceptions
    SET modify_date = CURRENT_TIMESTAMP
    WHERE EXISTS
    (
        SELECT 1 FROM inserted
            WHERE PK_column = cp_Exceptions.PK_column
    )

You can also use a self-join

UPDATE cp_Exceptions

    SET modify_date = CURRENT_TIMESTAMP
    FROM cp_Exceptions c
    INNER JOIN inserted i
    ON i.PK_column = c.PK_column

All untested -- you forgot to provide DDL and sample data, see
http://www.aspfaq.com/5006





Show quoteHide quote

Show quote Hide quote I've asked a similar question in this group before except that the
ModifyDate field was working for me already I just wanted it to not update
when a row was updated to the same thing. I got several good replies and I
think I combined the suggestions from a couple of them to get something like
this

CREATE TRIGGER [MODIFY_DATE] ON [dbo].[cp_Exceptions]
FOR UPDATE
UPDATE cp_Exceptions SET MODIFY_DATE = GETDATE()
WHERE EXISTS
(SELECT * FROM DELETED
WHERE DELETED.ID = cp_Exceptions.ID
        AND (cp_Exceptions.Field1 != DELETED.Field1
        OR   cp_Exceptions.Field1 != DELETED.Field2
        OR etc)

You'll have to add additional checks for fields that can be null.

Michael

[OT] SQL Humor
composite key structure
Default Column Values
Updating Tables with Computed Columns - SET ARITHABORT
Need Query Help