Log LastChangedDate with trigger

CREATE TRIGGER logChanges
ON RepInfo
FOR UPDATE
AS
DECLARE @.RepInfoID INT
IF COLUMNS_UPDATED() > 0
UPDATE RepInfo
SET LastChangedDate = GetDate()
WHERE @.RepInfoID = RepInfoID
GO
--
Trying to update LastChangedDate in a given record when any of the columns
in the record are modified. The first time I ran this, I did not have any of
the "@.RepInfoID" clauses, and I go the whole column to update to GETDATE();
I
didn't want that, so I threw in the references to RepInfoID since it's the
identity column, trying to mimic some VB.NET code I have in another
application. No joy. This has to be a simple fix, but one that's elusive to
me. Any help is appreciated!
--
Thanks,
-AAAaron wrote:
> CREATE TRIGGER logChanges
> ON RepInfo
> FOR UPDATE
> AS
> DECLARE @.RepInfoID INT
> IF COLUMNS_UPDATED() > 0
> UPDATE RepInfo
> SET LastChangedDate = GetDate()
> WHERE @.RepInfoID = RepInfoID
> GO
> --
> Trying to update LastChangedDate in a given record when any of the columns
> in the record are modified. The first time I ran this, I did not have any
of
> the "@.RepInfoID" clauses, and I go the whole column to update to GETDATE()
; I
> didn't want that, so I threw in the references to RepInfoID since it's the
> identity column, trying to mimic some VB.NET code I have in another
> application. No joy. This has to be a simple fix, but one that's elusive t
o
> me. Any help is appreciated!
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Try this:
CREATE TRIGGER logChanges
ON RepInfo
FOR UPDATE
AS
- -- Assuming Columns_Updated() is a user-defined function
IF COLUMNS_UPDATED() > 0
UPDATE RepInfo
SET LastChangedDate = GetDate()
FROM inserted as i
WHERE i.RepInfoID = RepInfo.RepInfoID
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhqcxoechKqOuFEgEQKr4ACgvVYOXFpxFHW1
vT9osNSW/67oajAAnj9u
y8qk0gGECHy2AZQ9bWS00e6K
=Jj7k
--END PGP SIGNATURE--|||Thanks Pal, that works great!
Btw, IF (COLUMNS_UPDATED()) is not a user-defined function. Here's a clip
from SQL Server Books Online:
--<Snip>--
IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or
columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit
pattern that indicates which columns in the table were inserted or updated.
--<Snip>--
-AA
"MGFoster" wrote:

> Aaron wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Try this:
> CREATE TRIGGER logChanges
> ON RepInfo
> FOR UPDATE
> AS
> - -- Assuming Columns_Updated() is a user-defined function
> IF COLUMNS_UPDATED() > 0
> UPDATE RepInfo
> SET LastChangedDate = GetDate()
> FROM inserted as i
> WHERE i.RepInfoID = RepInfo.RepInfoID
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)
> --BEGIN PGP SIGNATURE--
> Version: PGP for Personal Privacy 5.0
> Charset: noconv
> iQA/ AwUBQhqcxoechKqOuFEgEQKr4ACgvVYOXFpxFHW1
vT9osNSW/67oajAAnj9u
> y8qk0gGECHy2AZQ9bWS00e6K
> =Jj7k
> --END PGP SIGNATURE--
>sql