a trigger question

Question

a trigger question

jenniferhu46

SSCrazy

Points: 2990
More actions
April 19, 2005 at 10:45 am

#164329

Hi experts,
I have an asp.net application which will insert date value to my SQL Server 2000 table and when it inserts a null value and the table will put 1/1/1900, which seems like a default value......
So, I am thinking to have a trigger to fix this problem, but I am not familiar to the T-SQL trigger.
I need to check if the inserting puts any '1/1/1900' into 6 columns, but any of them is '1/1/1900' can represent other 5 columns. It seems like
Update table set date1=null where date1='1/1/1900'
Update table set date2=null where date2='1/1/1900' ........
who to put into 1 trigger?
The following trigger is not correct....
--------------------------------------------------------------------
CREATE TRIGGER DefaultDate2 ON [dbo].[Crew_App_Detail_Table]
FOR INSERT
AS
Update Crew_App_Detail_Table set App_WorkFrom1 = null,App_WorkTo1=Null,
App_WorkFrom2=Null, App_WorkTo2=null,App_WorkFrom3=null, App_WorkTo3=null
where App_WorkFrom1 = '01/01/1900' or App_WorkTo1= '01/01/1900' or
App_WorkFrom2= '01/01/1900' or App_WorkTo2= '01/01/1900' or App_WorkFrom3= '01/01/1900'
or App_WorkTo3= '01/01/1900'

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

You dont need to use a trigger for this particular situation. You can fix it from the ASP.NET application itself. When you are passing in a value from the front-end and your value is null you can use SQLDatetime.Null to put in a NULL in the datetime column rather than writing a trigger to change the default 1/1/1900 into NULL.

If you post the front-end code you have where you are passing the value we can help you out on how to fix it.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 2

I have tried different ways to convert it to null, but when SQL Server sees null value in the smalldatetime field, it seems put a default value 1/1/1900 automatically.

The way I've tried can't bring me any luck:

Dim Birthday As Date

If Session("DOB") Is DBNull.Value Then

Birthday =

Nothing

Else .......

'This will insert '1/1/1900'

or

Dim Birthday As Object

If Session("DOB") Is DBNull.Value Then

Birthday = SqlDateTime.Null

Else ......

'this gives an error: value of type 'system.data.sqltypes.sqldatetime' cannot be converted to 'date'

dongadoy SSC Eights! Points: 870 More actions · Answer 3

SQL Server translates a blank or empty string to 1/1/1900. See what I mean: select convert(datetime, '')

I've had problems with UI developers that just pass empty text box values to an insert statement. The UI really needs to apply validation and default appropriate for the app.

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 4

you can do :

Birthday = Sqldatetime.Null ' or even dbnull.value might work

And you would need to import the sqltypes namespace for this.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

dongadoy SSC Eights! Points: 870 More actions · Answer 5

It's probably at the insert/update statement that you need to handle null issue. If you check the SQL that ASP app is generating, it's probably converting the variable or object to an empty string.