Wednesday, March 21, 2012

How to create a trigger to update a field

Hi -

I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.

Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.

Is there a way to do this with a trigger (or an otherwise elegant way)?

Thanks in advance for ANY help or ideas.


Oliver

Why do you need a trigger for that? Cant your application/stored proc handle the logic? Triggers are generally a maintenance nightmare (IMHO). You have to use them cautiously if you have to.

|||

Thanks for the advice. Oliver

|||

I agree with ndinakar, stay away from triggers.

Your best bet is to handle the logic in your app or stored proc. For a stored procedure you could handle it as so

Create Procedure dbo.myUpdateProc( @.fieldIdentityint, @.RequestIDbigint =NULL)If @.RequestIDISNOT NULLBegin Update <table>set RequestID = @.RequestID, Booktime =GetDate()where = @.fieldIdentityEnd

sql

No comments:

Post a Comment