June 19, 2008 at 11:53 pm
Hello
I have an application which imports data from a text file which has lacs of records. There is a field name "effect_date" in the table. The application has its own logic to import this data as the text file is also created by the same application from other database. When I try to import the data it stops at certain row where the "effect_date" is not proper say "01-Jan-0545". I want to create a trigger which checks the value of inserted column and assign "Null" or any default date which accept SQL 2005. Can anybody suggest how to write the code.
Regards & Thanks
June 20, 2008 at 5:34 am
You could do this with an INSTEAD OF trigger. Look it up in books online, the documentation for the syntax is pretty thorough and includes samples.
June 20, 2008 at 5:48 am
As was pointed out to me the other day (after I said the same thing in the same situation), data types are checked before the instead of trigger fires, in order to create the inserted table
You can use the instead of, but you will need 2 columns (one a varchar column that the app puts the bad value into and one that the instead of populates with the correct values.
Can you not put the checks in th application?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 22, 2008 at 9:20 pm
We cannot change the application logic, That is the big issue. we do not have source code of the application. And also we cannot change table structure otherwise it will create hell lot of work for us. we have many other tables also having the same issue. let me check with instead of trigger, but I dout it will work.
Thanks & Regards
June 22, 2008 at 11:12 pm
This is why imports should always be to staging tables of NVarchar.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 22, 2008 at 11:55 pm
Import into a staging table, do your clean up, move data to a proper table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2008 at 1:50 pm
Exactly.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply