[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Update trigger on condition

Posted on 2000-01-24
4
Medium Priority
?
282 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:earlM
  • 2
4 Comments
 
LVL 4

Expert Comment

by:Gustavo Perez Buenrostro
ID: 2383636
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
 

Author Comment

by:earlM
ID: 2384163
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
 
LVL 3

Accepted Solution

by:
ahoor earned 400 total points
ID: 2384569
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
 

Author Comment

by:earlM
ID: 2386061
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question