| Author |
 Topic  |
|
|
htrott
Starting Member
2 Posts |
 Posted - 07/04/2012 : 06:54:18
|
Hi,
This may or may not be possible...
I have an API, which I can't modify the code for, This application updates an MSSQL table with a datetime value, However sometimes it passes the wrong format (mm/dd and dd/mm switched) and the therefore the data doesn't get saved.
I was trying to catch the information before it gets inserted with a trigger. like this... But its not working, is there another way of doing this?
create trigger UpdatesDateFix on Updates
INSTEAD OF INSERT
AS
BEGIN
DECLARE @New_Routine as varchar(100),@New_NewData as varchar(100),@New_Function as varchar(100)
SELECT @New_Function=[function],@New_NewData=[Newdata],@New_Routine=[Routine] from inserted
INSERT INTO Updates ([Routine] ,[Function] ,[NewData] ,[UpdateTime]) Values (@New_Routine,@New_Function,@New_NewData,GETDATE())
END
Thanks,
Hayden
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/04/2012 : 09:45:43
|
I don't know of a way to get around this problem. Logically it seems as though the original insert operation gets completed and then the result is replaced with the code in the instead-of trigger. If that is the case, when you have date and month swapped, that would result in invalid cast even if you have the instead of trigger.
I would try these in this order:
1. Take the issue to the higher-ups and have the person(s) who wrote the crappy client code/API fix it.
2. Change the data type of the column to character, let the data get inserted and then use a trigger to fix it and may be even put the correct date value into another column. This is a bad practice, I don't like it, but I don't see another option.
3. Accept defeat. |
 |
|
|
htrott
Starting Member
2 Posts |
Posted - 07/04/2012 : 10:16:15
|
| Looks like 3 is my only option :( . Thanks for your input.. |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 07/04/2012 : 10:26:02
|
quote:
Originally posted by htrott
Looks like 3 is my only option :( . Thanks for your input..
That was just my opinion - there are some people who hang around on this forum who have really in-depth knowledge of SQL Server and neat tricks up their sleeves. Because today is July 4th (a holiday in USA) many of them may be away or on vacation. Give it at least another day to see if there are any suggestions from any of them. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 07/04/2012 : 14:41:09
|
The real problem is in the application that is inserting the data, so that is where it needs to be fixed.
If the data is bad, anything you do in a trigger would just be a guess about what it really should be.
Let the developer or vendor fix their application, or if they won't or can't then it's a business decision about what to do about it.
CODO ERGO SUM |
|
|
| |
Topic |
|
Catching DateTime error with a Trigger
