SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored Procedure to update a datetime field		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Starting Member

USA
32 Posts
Posted - 03/14/2013 :  13:37:14  Show Profile  Reply with Quote
Good afternoon,
Database="ncos"
I have a table with a fields named "unique_id","Status","Status_Updated","Op_Won"

What I am looking for is a stored procedure to update the datetime field "Status_Updated" when the field "Status" gets changed.

Also, if the field "Status" gets changed to "Won" then I would like to populate the field "Op_Won" with the current datetime.

Any ideas...and thanks in advance.


Bryan Holmstrom

chadmat
The Chadinator

USA
1957 Posts
Posted - 03/14/2013 :  13:53:52  Show Profile  Visit chadmat's Homepage  Reply with Quote
If you have strict control over the manner in which this table can be modified (Like, it can only go through an SP you write) then you could handle all of that logic in the Stored Proc that modifies that table. But the safer option would be a trigger.

-Chad
Go to Top of Page

bholmstrom
Starting Member

USA
32 Posts
Posted - 03/14/2013 :  14:51:35  Show Profile  Reply with Quote
I am using a trigger during update but.....here is the story as of now:

I have a procedure:

CREATE PROCEDURE [UPDATE_STATUS]
(
@RecID int

)
AS
Update ncos_Plain SET NC_Status_Updated = getdate()
WHERE NC_UniqueID = @RecID
GO

--------------------------------------------

This procedure is getting called by a trigger on update:

CREATE TRIGGER [update_status_date] ON [dbo].[NCOS_PLAIN]
FOR UPDATE
AS
EXEC UPDATE_STATUS NC_UNIQUEID

The system throws an error:

Error converting data type nvarchar to int.

The field nc_uniqueid is a integer.

Beyond that I would REALLY only like to update the field when one fields is altered.

Any thoughts ideas......Thanks



Bryan Holmstrom
Go to Top of Page

Paramasivan B
Starting Member

India
4 Posts
Posted - 03/15/2013 :  02:41:26  Show Profile  Reply with Quote


Give the DATETIME DATATYPE for NC_Status_Updated...
just check

Paramasivan B
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000