How to make a trigger refer to the current record

I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()

Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated... for example:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()
where shipto_id = @.THIS_ID

I imagine there's some kind of builtin variable or something like that. How is this done?

Thanks in advance.CREATE TRIGGER tr_cp_shiptos_u ON dbo.cp_shiptos
FOR UPDATE
AS
BEGIN
UPDATE c
SET date_updated = GETDATE()
FROM cp_shiptos c
JOIN inserted i
ON i.key = c.key
END
GO

How to Log Locks within a Database

I was wondering if there was a simple script that could be run or a way to h
ave an entry created in a log file whenever a SQL Lock occurs. Specifically
if it could also give the username of the user running the query that creat
ed the lock. Thank you.You could have a profiler trace running. But be aware that the locking
activity in SQL Server can be *very* high! Do a test first so you don't
overload the system with the logging.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.|||Tibor, Thanks for the reply. I tried profiling but noticed the performance
decrease. All I really would like is basically something that alerts me via
either a log file I can check daily or a netsend message that tells me when
the database gets blocked
by a SPID and who it is that is causing the block. I don't actually need to
monitor every lock as I found out with the profiler. Thank you.|||INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/defaul...kb;EN-US;271509
Also have a look at
INF: Troubleshooting Application Performance with SQL Server
http://support.microsoft.com/defaul...kb;EN-US;224587
HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later
http://support.microsoft.com/defaul...kb;EN-US;243589
INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
http://support.microsoft.com/defaul...b;EN-US;Q224453
As well as these articles themselves, they contain links in them to lots
of other performace troubleshooting type articles. Lots of good stuff !
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:3A5B46FE-B873-4AD4-8078-27C7B2DC885D@.microsoft.com...
> I was wondering if there was a simple script that could be run or a way to
have an entry created in a log file whenever a SQL Lock occurs.
Specifically if it could also give the username of the user running the
query that created the lock. Thank you.