Update field in new record based on value of another field in the same record

[Richard]

I want to update a field when a new timecard record comes in based
upon the value in another field on the same record and the value of
another field in a different table.

If I were writing a SQL statement for after the fact it would be:

update timecard set tudef1='JP0' where tudef5='JP' and tmatter in
(select mmatter from matter where mloc<>'30')

but I sure would like to do it with a trigger when the original input
happens.

Thanks in advance.

Rich

[Erland Sommarskog]

CREATE TRIGGER timecard_tri ON timecard AFTER INSERT, UPDATE AS

UPDATE timecard
SET tudefl = 'JPO'
FROM timecard t
WHERE EXISTS (SELECT *
FROM inserted i
WHERE t.key = i.key
AND i.tudef5 = 'JP'
AND i.tmatter in (select m.mmatter
from m.matter
where m.mloc<>'30'))

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx