Showing posts with label lastmodified. Show all posts
Showing posts with label lastmodified. Show all posts

Friday, March 23, 2012

lastmodified

Hi,
Can someone provide me a simple trigger where it updates a lastmodified
column.
It should update the column lastmodified whenever an update occurs on all
the other columns.
ThnxCREATE TRIGGER tr_YourTrigger
ON YourTable
FOR UPDATE
AS
IF @.@.ROWCOUNT > 0
UPDATE YourTable SET [LastModified] = GETDATE()
WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
Andrew J. Kelly SQL MVP
"Jason" <jlewis@.hotmail.com> wrote in message
news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Can someone provide me a simple trigger where it updates a lastmodified
> column.
> It should update the column lastmodified whenever an update occurs on all
> the other columns.
> Thnx
>|||In addition to Andrew's response you can use DEFAULT constraint
CREATE TABLE #Test
(
col DATETIME DEFAULT GETDATE()
)
INSERT INTO #Test DEFAULT VALUES
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> CREATE TRIGGER tr_YourTrigger
> ON YourTable
> FOR UPDATE
> AS
>
> IF @.@.ROWCOUNT > 0
> UPDATE YourTable SET [LastModified] = GETDATE()
> WHERE YourPK IN (SELECT b.PK FROM Inserted AS b)
>
> --
> Andrew J. Kelly SQL MVP
>
> "Jason" <jlewis@.hotmail.com> wrote in message
> news:O$XaerRGFHA.2568@.TK2MSFTNGP10.phx.gbl...
all[vbcol=seagreen]
>|||That only works for Inserts. He specifically asked for Updates.
Andrew J. Kelly SQL MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
> In addition to Andrew's response you can use DEFAULT constraint
> CREATE TABLE #Test
> (
> col DATETIME DEFAULT GETDATE()
> )
> INSERT INTO #Test DEFAULT VALUES
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eV5joCSGFHA.2976@.TK2MSFTNGP09.phx.gbl...
> all
>|||Thanks, my mistake.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:esONVXbGFHA.560@.TK2MSFTNGP15.phx.gbl...
> That only works for Inserts. He specifically asked for Updates.
> --
> Andrew J. Kelly SQL MVP
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uuRRBDYGFHA.2156@.TK2MSFTNGP09.phx.gbl...
lastmodified[vbcol=seagreen]
>

Last Update date/time

Hello.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

Last Update date/time

Hello.
Does SQL Server 2000 store somewhere the date/time that a table was last
modified? How about when the data in the table was last updated?
How can I view this information if so?
Thanks in advance,
Mike
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||You could use a TIMESTAMP attribute, but this has been turned into a table
modification version number in stead of the old DATE/TIME value once
supplied. It can tell you if a row has been modified before or after other
rows though.
Sincerely,
Anthony Thomas

"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eWRWXgtNFHA.3716@.TK2MSFTNGP14.phx.gbl...
> Does SQL Server 2000 store somewhere the date/time that a table was last
> modified? How about when the data in the table was last updated?
No and no.
Your options:
- auditing software, e.g. Entegra from www.lumigent.com
- setting up your own triggers
- profiler
- reading the log after the fact
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.