August 24, 2011 at 8:26 am
forsqlserver (8/24/2011)
Thanks TOMBut I am asking about the if else condition:
Suppose in one row at one time one startdate is coming but other termination date and officiatingstartdate is coming as null.then the update query should run only for startdate.
That can be implement using if else
It's often better to put conditions in the where clause of a query than in an if statement. The lines I wrote will each do nothing when the corresponding date is null or '' or already has had 'T:00:00:00.000' added (you don't want to add it twice) because the where clauses exclude null dates and dates including a "T".
If you want to use "if" statements you could do it looping through the inserted table, populating a local variable for each row (in which case you need to set nocount off at the start of the trigger or nasty things are likely to happen)- of if there's never going to be any insert or update that affectis more than one row you can build that assumption into the trigger to avoid having the loop, you can either use one call to COLUMS_UPDATED, assigning the result to a local variable (so you must set nocount on) or three calls to UPDATE() avoiding the local variable and the need to set nocount. But it's more complicated than doing it in the where clause.
Tom
August 24, 2011 at 8:29 am
forsqlserver (8/24/2011)
Error coming in update is:Msg 8152, Level 16, State 14, Procedure S3, Line 20
String or binary data would be truncated.
The statement has been terminated.
================================Edited==============
I am getting this error if I am updating a value that is is stored in the table in 02-02-2001T00:00:00.000 format otherwise its fine working.
OK, that's because you haven't added "and ....date not like '%T%' " to the where clause (where "....date" means whichever date field is updated in that statement).
Edit: I see I had StartDate in all three lines, instead of changing it to TerminatingDate or whatever in my last post but one. I've edited it, should be correct now.
Tom
August 24, 2011 at 11:36 pm
Dear ToM,
I am getting this error if I am updating a value that is is stored in the table in 02-02-2001T00:00:00.000 format otherwise its fine working.
OK, that's because you haven't added "and ....date not like '%T%' " to the where clause (where "....date" means whichever date field is updated in that statement).
Its not possible my friend there are situations when I have to update a already saved date in 02-02-2001T00:00:00.000 format.
Help..
Thanks
August 25, 2011 at 2:52 am
Jeff Moden (8/19/2011)
forsqlserver (8/18/2011)
Thanks Rozema and chris: My requirement is:I have two databases one is of HRMS application and another for forefront application.Now some table data comes from HRMS database to forefront database in which date column also comes.
Forefront accept date in the format of 2011-12-02T00:00:00 and forefront admin wants, whatever date is coming from HRMS database table a trigger should be configured on insert and update of that table and convert the format to 2011-12-02T00:00:00
Can u help me in creating trigger for this?
Gosh... I'll say it again... storing formatted dates in SQL Server is "death by SQL". Store the dates as a DATETIME datatype and if you really must have formatted dates for something else, forget about triggers and create a View to do the conversions.
How much longer before you guys actually believe him us?
August 25, 2011 at 2:59 am
It's a presentation issue!
Store the data using DATETIMEOFFSET, DATETIME2(3) or similar datatype.
Then add a calculated columns (persisted?) for the visual representation you desire.
N 56°04'39.16"
E 12°55'05.25"
August 25, 2011 at 3:02 am
Yeah, that's what we've been saying too. He's already falling into all the pits that we've warned him for.
August 25, 2011 at 8:18 am
forsqlserver (8/24/2011)
Dear ToM,I am getting this error if I am updating a value that is is stored in the table in 02-02-2001T00:00:00.000 format otherwise its fine working.
OK, that's because you haven't added "and ....date not like '%T%' " to the where clause (where "....date" means whichever date field is updated in that statement).
Its not possible my friend there are situations when I have to update a already saved date in 02-02-2001T00:00:00.000 format.
Help..
But the trigger shouldn't do anything if the date is already in that format - your problem is that it is doing something, and adding that condition to the where in the trigger just tells it not to do anything in that case, so that the update that caused the trigger to fire will happen without the trigger modifying anything unless the trigger needs to modify it.
Tom
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply