how to interrogate INSTEAD OF UPDATE trigger failure?

[voirin]

Hi, I was hoping someone could help me...

I have a INSTEAD OF UPDATE trigger set up against a view:


CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
UPDATE mytable SET
field1 = myfunction(inserted.field1),
field2 = myfunction(inserted.field2),
field3 = myfunction(inserted.field3),
field4 = ....
....
....
FROM inserted
WHERE mytable.userid = inserted.userid


The trigger has a fairly huge 'UPDATE mytable SET' statement that has around
50 'column_name = expression' statements. The whole UPDATE / SET statement
spans over 50 lines.

Why am I saying all the above? Well I wanted to clarify something...

I get the error:

Server: Msg 8115, Level 16, State 2, Procedure mytrigger, Line 14
Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated.

Line 14 contains the 'UPDATE mytable SET' statement. Does the above error
mean that the error is occurring directly at line 14, or somewhere within my
*whole* UPDATE / SET statement?

How can I interrogate the error? It doesn't look simple from the outset. I
understand that smalldatetime must be between 1900 and 2079. I just cant see
what could be going wrong.

Is it possible to use SQL Profiler to see what is happening within my UPDATE
/ SET statement?

I have thought of altering the trigger (as an interim method towards
debugging the cause of the error) to do the following:


CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
SELECT
myfunction(inserted.field1) AS field1,
myfunction(inserted.field2) AS field2,
myfunction(inserted.field3) AS field3,
....
....
....
FROM inserted
WHERE mytable.userid = inserted.userid
SELECT * FROM deleted
SELECT * FROM inserted


and then I would execute a simple update statement which I know would cause
an arithmetic overflow error as stated above, e.g. something like:


UPDATE mytableview
SET field2 = 1234
WHERE field1 = 5


So in this regard I can at least see what field might be causing the
arithmetic overflow error.

Is this a good way of interrogating the cause of the problem?

Does anyone else have a better method?

Any help most appreciated!

Cheers,
voirin

 

[SQLBill]

First thing to remember, the line error isn't always the line counted from the top. Run the query in Query Analyzer, when you get the error in the message pane, double click on the ERROR. It will highlight the area where it THINKS the problem is.

Look into the lines around the highlighted line.

-SQLBill

Posting advice: FAQ481-4875

 

[voirin]

Hi,

Yep, but when I enter the query:


UPDATE mytableview
SET field2 = 1234
WHERE field1 = 5


... in Query Analyzer, and get the error ...


Server: Msg 8115, Level 16, State 2, Procedure mytrigger, Line 14
Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated.


Double clicking on the error would at most highlight something within that little select statement above i.e. it will not highlight anything within the trigger itself. It is the code within the trigger that I want to interrogate.

Any help most appreciated!

Cheers,
voirin