?
Solved

How do I use a Trigger to update a single field in sql2005

Posted on 2009-02-23
7
Medium Priority
?
164 Views
Last Modified: 2012-05-06
I have a sql 2005 database where I record inventory.  I want to update a field in the table with a date when another field in the record is updated and the value = 1.

So, if a record is updated and FIELD1 was changed to a certain VALUE then set FIELD2 = getdate()  

I only want to update the field when the otherfield is first equal to 1.

I dont know how to write triggers and need help.
- =============================================
CREATE TRIGGER TriggerDateCompleted
   ON  Jason_DB.dbo.sales_jobentry
   AFTER UPDATE
AS 
BEGIN
	
	SET NOCOUNT ON;
 
   update sales_jobentry  
set  JobCompletedDate = getdate() 
 
 
 
END
GO

Open in new window

0
Comment
Question by:handyjay
  • 3
  • 3
7 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23715016
You need to join to the INSERTED table and add a where clause.  Just update PRIMARYKEY with your Primary Key(s).
CREATE TRIGGER TriggerDateCompleted
   ON  Jason_DB.dbo.sales_jobentry
   AFTER UPDATE
AS 
BEGIN
        
        SET NOCOUNT ON;
 
   update sj
   set  JobCompletedDate = getdate() 
   from sales_jobentry sj
     join inserted i
       on sj.PRIMARYKEY = i.PRIMARYKEY
   where i.Field1 = 'SomeValue'
 
 
 
 
END
GO

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 23715028
try this:

- =============================================
CREATE TRIGGER TriggerDateCompleted
   ON  Jason_DB.dbo.sales_jobentry
   AFTER UPDATE
AS
BEGIN
      
      SET NOCOUNT ON;
 
   update s
set  JobCompletedDate = getdate()
      from sales_jobentry   s
      join inserted i on s.primarykeyfield = i.primarykeyfield
      where i.field1 <> s.field1 and s.field1 = 'somevalue'
 
 
 
END
GO

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23715039
chap:  If i.field1 is NULL and updated to 'SomeValue' then yours won't work :).
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 60

Expert Comment

by:chapmandew
ID: 23715046
quick change on my end....change to make sure the newvalue is different than the old value.

try this:

- =============================================
CREATE TRIGGER TriggerDateCompleted
   ON  Jason_DB.dbo.sales_jobentry
   AFTER UPDATE
AS
BEGIN
     
      SET NOCOUNT ON;
 
   update s
set  JobCompletedDate = getdate()
      from sales_jobentry   s
      join inserted i on s.primarykeyfield = i.primarykeyfield
join deleted d on s.primarykeyfield= d.primarykeyfeild
      where d.field1 <> i.field1 and s.field1 = 'somevalue'
 
 
 
END
GO
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23715050
sorry... if either i.field1 or s.field1 is null.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 23715057
actually, my first one will never work since Im comparing the table's current values to the inserted table (current values).  They'll always be equal.  :)
0
 

Author Closing Comment

by:handyjay
ID: 31550272
Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

862 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