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

Richard (richard.mogy (AT) gmail (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) 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