Problem with datetime in trigger

[pxh12]

Hi,

I'm trying to write a trigger that will create a record in a table when a value is changed.

When I fire the trigger, I get this error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

The problem lies in the passing of a date to a stored procedure, but it only occurs when I call the SP from the trigger. If I replicate the process in a normal T-SQL query, it works fine. Can anybody help?

Trigger Code:

ALTER TRIGGER [dbo].[CreatePropertyTasks]
ON [dbo].[DW_PROPERTY]
FOR INSERT, UPDATE

AS
DECLARE @DW_DT_UID AS nvarchar(10)
DECLARE @DW_PR_ADDRESS AS nvarchar(100)
DECLARE @DW_PR_REALIZATION AS decimal
DECLARE @DW_PR_REALIZATION_DATE AS datetime

DECLARE @TaskDetail AS nvarchar(100)

IF UPDATE(DW_PR_REALIZATION)
BEGIN
SELECT @DW_DT_UID = DW_PR_DT_UID,
@DW_PR_REALIZATION_DATE = DW_PR_REALIZATION_DATE,
@DW_PR_REALIZATION = DW_PR_REALIZATION,
@DW_PR_ADDRESS = DW_PR_ADDRESS1
FROM inserted

DELETE FROM DW_TASK
WHERE (DW_TK_UID = @DW_DT_UID)
AND (DW_TK_TT_UID = 29)
AND ((DW_TK_DESC LIKE 'Lump Sum from property realisation due')
OR (DW_TK_DESC LIKE 'Check availability of Lump Sum%'))

SET @TaskDetail = 'Lump Sum from property realisation due'

-- Insert Task on Due Date
EXEC UpdateTask
@DW_TK_UID = -1,
@DW_TK_TT_UID = 29,
@DW_TK_DT_UID = @DW_DT_UID,
@DW_TK_USER_NAME = '',
@DW_TK_DESC = @TaskDetail,
@DW_TK_SCHEDULED = 'Y',
@DW_TK_SCHEDULED_TIME = @REALIZATION_DATE,
@DW_TK_ACTION = @TaskDetail,
@DW_TK_COMPLETED = 0

END



T-SQL Code:

DECLARE @DW_DT_UID AS nvarchar(10)
DECLARE @DW_PR_ADDRESS AS nvarchar(100)
DECLARE @DW_PR_REALIZATION AS decimal
DECLARE @DW_PR_REALIZATION_DATE AS datetime

set @dw_dt_uid = 36106

print @dw_dt_uid

SELECT
@DW_PR_REALIZATION_DATE = DW_PR_REALIZATION_DATE,
@DW_PR_REALIZATION = DW_PR_REALIZATION,
@DW_PR_ADDRESS = DW_PR_ADDRESS1
FROM DW_PROPERTY
WHERE DW_PR_DT_UID = @DW_DT_UID

exec UpdateTask
@DW_TK_UID = -1,
@DW_TK_TT_UID = 29,
@DW_TK_DT_UID = @DW_DT_UID,
@DW_TK_USER_NAME = '',
@DW_TK_DESC = 'Test Task using SP',
@DW_TK_SCHEDULED = 'Y',
@DW_TK_SCHEDULED_TIME = @DW_PR_REALIZATION_DATE,
@DW_TK_ACTION = 'Test Task using SP',
@DW_TK_COMPLETED = 0

 

[gmmastros]

You have bigger problems than a datetime conversion. Your trigger assumes that a single row is being inserted/updated. If multiple rows are inserted or updated within the same query, your procedure will only handle one row. This will likely cause problems with your data. I strongly encourage you to fix this issue as soon as possible.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pxh12]

Hi gmmastros,

Thanks for your reply.

The application only allows one row to be updated at a time, so there is no need to handle multiple row updates.

 

[gmmastros]

That's a dangerous way of looking at this. I believe you when you say the application only allows a single row to be updated at a time, but I would argue that it does that NOW. Applications have a tendency to change over time. What's true now may not be true later.

Regardless, I gave you advice. It's up to you whether you want to follow it or not.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

Listen to George, never , never , never write a trigger to handle only one row inserts. Someday someone will need to do a batch insert and you will lose data integrity. This is a VERY POOR practice. Do not do it.

I find it is also not a good idea to call a stored proc from a trigger. You don't want any outside objects that might cause a trigger to break.

Now on to your problem. What datatype is DW_PR_REALIZATION_DATE in the table the trigger is on? If it is not datetime, then you may have an iisue with someone putting bad data into the field that cannot convert. Your trigger must handle this if you are not able to correctly change the table structure instead.



"NOTHING is more important in a database than integrity." ESquared

 

[pxh12]

Hi SQLSister,

I'll concede to the better advice given here and rewrite the trigger to handle multiple rows once I've solved this problem.

DW_PR_REALIZATION_DATE is a datetime in its table. The data is validated in the application so only a datetime can be entered.

I have tried doing using an insert query rather than the SP, but get exactly the same error.

 

[SQLSister]

Are you by chance going from datetime to smalldatetime?

Hmmm here's what I do to troubleshoot a trigger:

First I create a temp table called #inserted with the same structure as the table the trigger is on.

Then I insert the values I would expect to see inthat table (the ones you are using for your test are good).

Now I replace inserted with #inserted. and comment out the create trigger part.

I can now add whatever I need to see the values I am concerned with at this point. I can also try multiple things until I work out what i want it to do. I can run one step at a time and see what I've got. Once everything is fine, I change #inserted to inserted and put the code back into create trigger.


"NOTHING is more important in a database than integrity." ESquared