Trigger?

Hassano563658

Hello Team

i want to make a trigger to delete the orderdate when inserting the record ( if the orderdate is 1/1/1900)

Thanks lot

TimDBA

This trigger will work for an insert only. You'll need to add FOR INSERT, UPDATE to make it work for an update statement

CREATE TRIGGER tr_TriggerName ON TableName

FOR INSERT

AS

BEGIN

DELETE tbl

FROM TableName tbl

JOIN INSERTED i ON tbl.PrimaryKeyField = i.PrimaryKeyField

WHERE CONVERT(varchar(10), OrderDate, 101) = '01/01/1900'

END

GO

Of course, instead of doing this, you could always create a constraint on this field that doesn't allow that date.

Tim

Madhu K Nair

basically, this problem comes when you pass the parameter as char(0) or ' ' to the sp or adhoc query. This always happens due to ignorance.

CREATE TABLE dbo.t2(col1 int, col2 datetime);

INSERT INTO dbo.t2 SELECT 1, null

INSERT INTO dbo.t2 SELECT 2, getdate()

INSERT INTO dbo.t2 SELECT 6, '' -- This statement will insert the datetime as 1900-01-01 00:00:00.000 because you have supplied char (0) string

GO

select *from t2

so the best way to restrict is to create a Check constraint and also supply null if there is no value in the frontend text box. I don't think you need to write trigger to do this. If you want to write it in trigger use Case statement to check the value (which i will never suggest)

Madhu

Arnie Rowland

Hassano wrote:

delete the orderdate ( if the orderdate is 1/1/1900)

And as Madhu suggested, any datetime value passed in as an "empty string" [''] will be 'assumed' to be to be zero, which translates to '01/01/1900 00:00:00.000'. That happens because a datetime value is inherrently a number, and since numbers can't have string values, even empty strings, SQL Server converts the empty string to zero.

Your alternative would be to use a TRIGGER to 'translate' the value of '01/01/1900 00:00:00.000' to NULL. Most of the time, it is better to have NULL to indicate missing (or unknown) values -though it does then take a little extra effort to account for NULL when doing various data routines.

A 'better solution would be to have your application check the textbox value, and it if is a empty string, then assign the parameter dbNULL instead of the textbox value.

Hassano

Kindely tell me how to use a TRIGGER to 'translate' the value of '01/01/1900 00:00:00.000' to NULL

Thankslot

Manivannan.D.Sekaran

may be this,

Code Snippet

CREATE TRIGGER tr_TriggerName ON TableName

FOR INSERT

AS

BEGIN

Update TableName

Set OrderDate = null

WHERE CONVERT(varchar(10), OrderDate, 101) = '01/01/1900'

-- and YourPrimarykeycolumn = (select YourPrimarykeycolumn from inserted)

END

GO

Madhu K Nair

you need to understand why system is automatically taking this date .. that has already been discussed in previous post. Still u want to use trigger, then use CASE Function

declare @dt datetime

--set @dt=' '

set @dt=getdate()

SELECT

CASE

WHEN len(@dt)=0 THEN null

else @dt

END

set @dt=' '

SELECT

CASE

WHEN len(@dt)=0 THEN null

else @dt

END

in same style you can check the len(yourcolumnname)=0 then null else column

still you could not follow post the script of your trigger(if you already have one) or your table structure

Madhu