update getting confused with a trigger name

[kjuenke]

We use Visual MFG v6.2.8 on a Microsoft SQL Server. There is a table named purc_order_line that has a trigger defined as update_purc_order_line. When I try to run an update script on the table I get the error: Server: Msg 50000, Level 16, State 1, Procedure UPDATE_PURC_ORDER_LINE, Line 182
VMFG-30903 error in trigger UPDATE_PURC_ORDER_LINE. I am guessing the update command is somehow getting confused with the trigger name. Is this correct? If so, how can I run an update script on this table? Thanks in advance for any assistance!

 

[JohnBates]

I am guessing the update command is somehow getting confused with the trigger name."

It really shouldn't be. SQL Server can tell the difference between the 2 object types. You might try naming the trigger something else, just to know for certain.

I'd guess there is something wrong with the proc or the trigger itself.

Why not run the proc in Query Analyzer to be certain it works correctly?

HTH. John

 

[kjuenke]

Thanks for the response.

If I delete the trigger named update_purc_order_line I can successfully execute the statement update PURC_ORDER_LINE set USER_8 = 'N' where USER_8 = 'Y' in Query Analyzer. Of course, I can't do this with our production database!

Ken

 

[SQLSister]

what is the trigger doing? It is possible the error is in the trigger or you are not giving it some information it needs to do the processing in the trigger. One possibility is the trigger is written badly and only handles single record updates and you are doing a multiple record update and so something is failing probably due to a data integrity issue or a violation of a defined foreign key relationship.

Questions about posting. See faq183-874

 

[kjuenke]

Thanks to all,

I fixed my problem by doing this:

alter table purc_order_line disable trigger UPDATE_PURC_ORDER_LINE
update purc_order_line set user_8 = 'N' where user_8 = 'Y'
alter table purc_order_line enable trigger UPDATE_PURC_ORDER_LINE

Ken

 

[JohnBates]

Glad that you were able to get it fixed, kjuenke.

I don't use triggers so I could not offer much help.

John

 

[SQLSister]

If you solved your problem by disabling the tirgger you may have created a bigger problem in data integrity. What exactly is the trigger doing? YOu really need to discover why it was failing and how to fix that problem not cover it up by temporarily disabling it. What ever the trigger does, has now not been done for those records you updated. This could be disatrous in terms of data integrity, if there is some other table dependent on being updated when this table is updated. What you did is a very bad practice and extremely dangerous.

Questions about posting. See faq183-874

 

[kjuenke]

This could be disatrous in terms of data integrity, if there is some other table dependent on being updated when this table is updated. What you did is a very bad practice and extremely dangerous."

Thanks for the tip. I won't do what I planned until I investigate further.

Ken