Trigger problem
-
I have a trigger on an orderLine table that runs the DATEDIFF() function on two dates and uses the returned value in a calculation. I have the trigger simplified to only performing the following:
--------------------------------------
CREATE TRIGGER trig_insertupdate
ON [orderLine]
FOR INSERT, UPDATE
AS
insert into _test (column1) values (DATEDIFF(day,'09/17/05','09/16/05') )
---------------------------------------
when i insert through my ERP software, i get the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Native Error number: 3621
however, when i manually update or insert a row through enterprise manager, the trigger performs correctly and the value -1 is inserted into column1 of my _test table.
Does anyone have any idea what might be causing this?
Thanks!
Regards, Jim C
-
No offense, but why would you want to do that???
-
The trigger performs a whole mess of calculations, but i've narrowed it down to this part of it being the problem.. so that's what i've included here..
Regards, Jim C
-
Can we see the whole code?? there's just no way that this can take a long time (asssuming there's no locking problems).
-
Ok. ive found out more about this problem.... it works if i enter in a full year (2005 instead of 05)... I'd like to figure out why this is happening only when i perform an insert or update in the software and not in Enterprise Manager or Query Analyzer.
Here is the full Trigger:
there are 2 dates and a number that are being pulled out of a comment field. The field is always formatted the same. the substring(comment,15,8) and left(comment,8) pull dates while substring(comment,29,8) pulls a decimal. I know this would be much better off in its own field, but i can't change that - Legacy systems
CREATE TRIGGER trig_insertupdate
ON [ORDERLINE]
FOR INSERT, UPDATE
AS
IF @@rowcount = 0
return
declare
@ordtype char (1),
@ordno char (8),
@lineseqno char (2),
@itemno char (15),
@act char (8),
@actsgy char (8)
select
@ordtype = ord_type,
@ordno = orderNum,
@lineseqno = line_no,
@itemno = item_no
from inserted
if @ordtype <> 'O'
return
if @itemno in ('3631','3633','8642','9242','011','012')
begin
select
@act = substring(comment,29,8),
@actsgy = convert(decimal(6,3),substring(comment,29,8)* exp(log(2)
* datediff(day, substring(comment,15,8), left(comment,8))
/ (select case left(@itemno,4)
when '3631' then 59.43
when '011' then 59.43
else 16.99
end)))
from orderComments where ord_type = @ordtype and orderNum = @ordno
and line_seq_no = @lineseqno and comment_no = '1' if @act = '' or @act is null
return
else
update orderline set act = @act, actsgy = @actsgy where ord_type = @ordtype and orderNum = @ordno and line_no = @lineseqno
end
Regards, Jim C
-
1. if your software is using set language or any other Function depending on reginal settings you have the answer for your first question.
2. You should check with ISDATE the returned values to avoid conversion errors
* Noel -
Not to mention that this trigger will fail if you update more than one row at the time... Take a look at the inserted/deleted tables.
-
I didn't even look at the trigger code! You are absolutely correct.
Don't mean to bash anyone but I have lost the count of people that code ther triggers for just on row!!
* Noel -
I don't think they even invented a datatype to hold such a number
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply