|
Home > Archive > MS SQL Server > November 2006 > How to write a trigger for date last modified...
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to write a trigger for date last modified...
|
|
| dhooger@yahoo.com 2006-11-15, 7:14 pm |
| I'm in SQL Server 2000 and need to write a trigger for date last
modified. I've created a column (date_last_modified)
and have written
this so far...
CREATE TRIGGER DATE_LAST_MODIFIED ON [dbo].[TABLE_NAME]
FOR INSERT, UPDATE
AS UPDATE TABLE_NAME SET DATE_LAST_MODIFIED = GETDATE()
Of course, this updates every record and not just the inserted or
updated record.
How do I collect the key field(s) from the inserted or updated
record(s) to be put in a where clause?
| |
| Dan Guzman 2006-11-16, 12:16 am |
| > How do I collect the key field(s) from the inserted or updated
> record(s) to be put in a where clause?
The after images of the inserted/updated rows are in the inserted
pseudo-table, which you can use to identify the rows to be updated. For
example:
CREATE TRIGGER DATE_LAST_MODIFIED ON [dbo].[TABLE_NAME]
FOR INSERT, UPDATE
AS
UPDATE TABLE_NAME
SET DATE_LAST_MODIFIED = GETDATE()
WHERE TABLE_NAME.PK_COLUMN IN
(
SELECT inserted.PK_COLUMN
FROM inserted
)
--
Hope this helps.
Dan Guzman
SQL Server MVP
<dhooger@yahoo.com> wrote in message
news:1163636288.140465.75170@i42g2000cwa.googlegroups.com...
> I'm in SQL Server 2000 and need to write a trigger for date last
> modified. I've created a column (date_last_modified)
and have written
> this so far...
>
> CREATE TRIGGER DATE_LAST_MODIFIED ON [dbo].[TABLE_NAME]
> FOR INSERT, UPDATE
> AS UPDATE TABLE_NAME SET DATE_LAST_MODIFIED = GETDATE()
>
> Of course, this updates every record and not just the inserted or
> updated record.
>
> How do I collect the key field(s) from the inserted or updated
> record(s) to be put in a where clause?
>
| |
| Aaron Bertrand [MVP] 2006-11-16, 7:12 pm |
| Something like this (untested)
IF @@ROWCOUNT > 0
UPDATE t
SET t.DATE_LAST_MODIFIED = CURRENT_TIMESTAMP
FROM TABLE_NAME t
INNER JOIN inserted i
ON t.key = i.key
[AND so on ... for multi-column key]
BTW you should only need to do this for UPDATE, not INSERT. If you are
storing creation date in a separate column, then having a duplicate in the
modified is unnecessary. And if date_last_modified initially stores
creation date, then it would be better to handle this using a default
constraint instead of an insert trigger.
<dhooger@yahoo.com> wrote in message
news:1163636288.140465.75170@i42g2000cwa.googlegroups.com...
> I'm in SQL Server 2000 and need to write a trigger for date last
> modified. I've created a column (date_last_modified)
and have written
> this so far...
>
> CREATE TRIGGER DATE_LAST_MODIFIED ON [dbo].[TABLE_NAME]
> FOR INSERT, UPDATE
> AS UPDATE TABLE_NAME SET DATE_LAST_MODIFIED = GETDATE()
>
> Of course, this updates every record and not just the inserted or
> updated record.
>
> How do I collect the key field(s) from the inserted or updated
> record(s) to be put in a where clause?
>
|
|
|
|
|