Update trigger on condition

I am trying to create an update trigger to record when a row is first updated (Not appended) in an application.  (I need to record the date/time so that we can evaluate response time to calls on a call tracking application)  The key is that I only want to update the First_Worked column if it is null.
Sample is listed below of what I am trying.  It fails with an error on the IF deleted.First_worked saying that the prefix deleted does not match a tablename.

Any sample would be useful

CREATE TRIGGER trgFirstWorked ON [Cntct_Contacts]
FOR  UPDATE
AS
IF UPDATE(User_ID)
BEGIN
   SELECT * FROM deleted
   IF deleted.First_Worked IS NULL
   BEGIN      
         UPDATE  [Cntct_Contacts] SET  [Cntct_Contacts].First_Worked = GetDate()
           FROM  [Cntct_Contacts] INNER JOIN deleted
           ON [Cntct_Contacts].Call_No = deleted.Call_No      
     END
END
earlMAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustavo Perez BuenrostroCommented:
earlM,
Try code below and let me know your opinion:

create trigger trgFirstWorked on Cntct_Contacts
for update
as
begin
if update(User_ID)
  begin
    update Cntct_Contacts
       set Cntct_Contacts.First_Worked=getdate()
      from deleted
     where Cntct_Contacts.Call_No
          =deleted.Call_No
  end
end
0
earlMAuthor Commented:
The problem with not checking for the NULL value is that every update will cause the First_Worked field to be reset with the current date.  What I need is the first date that the record was updated and I would lose that information.
0
ahoorCommented:
EarlM

You should use a select clause like this:

CREATE TRIGGER trgFirstWorked ON [Cntct_Contacts]
FOR  UPDATE
AS
    IF UPDATE(User_ID)
    BEGIN
         --  SELECT * FROM deleted  (don't need this)
           IF exists (select 1 from deleted
                           where First_Worked IS NULL )
           BEGIN
                 UPDATE  [Cntct_Contacts]
                 SET  [Cntct_Contacts].First_Worked = GetDate()
                 FROM  [Cntct_Contacts] INNER JOIN deleted
                             ON [Cntct_Contacts].Call_No = deleted.Call_No
           END
     END

Ps why use caps?

Arjan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
earlMAuthor Commented:
Thanks, this worked fine.  (All caps because someone long ago specified that as a standard for my org.  I'm a C - C++ person so it drives me nuts also!)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.