All,
I'm trying to create a trigger that can function on a database that is in SQL 2000 compatibility mode due to the application that uses it requiring it. The application is Visual (version 7), an ERP system written by Infor. I've created one trigger successfully, but when I do so on the VENDOR_QUOTE table, anyone trying to update a vendor quote via part maintenance is unable to do so, as they get an error that is clearly caused by the trigger, so I've had to disable it. The error says the ARITHABORT setting is wrong, and I tried re-creating the trigger with that setting turned on ahead of the CREATE TRIGGER statement. That did not resolve the problem, so that's when I disabled the trigger and gave up for the time being. Anyone have any guidance on this? I'm wondering if I just need to figure out what the session settings are for when someone makes such an update by using Profiler to capture them, and then duplicating those settings prior to the CREATE TRIGGER statement? All thoughts welcome...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 25, 2019 at 6:06 pm
You can use SET ARITHABORT and other SET statements within the trigger itself. Those settings will be discarded when the trigger ends, so you can't do any harm to outside code because of changed settings.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Requires compat mode 80? Only supported up to SQL 2008, which is itself is out of support. Time to look for a replacement, I'm afraid!
As for the actual problem, it has nothing to do with the trigger itself, but something which is used in the trigger, implicitly or explicitly. There are a number of features which requires that the settings QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING and CONACT_NULL_YIELDS_NULL to be on. And under compat level 80, also ARITHABORT. These are the features that I recall on the top of my head.
Let's say that the table updates a table with a filtered index to raise this error.
ARITHABORT is more difficult than the other settings, because it is not on by default when you connect from an application. (But to confuse, SSMS sets is ON by default.) You can add SET ARITHABORT ON to the trigger, but it may not help, because the error may be raised when the trigger is compiled the first time. You can avoid this by pushing the offending operation to an inner scope with dynamic SQL, but that have other repercussions, not the least in a trigger, since you cannot access inserted/deleted from your dynamic SQL.
It will not help to have ARITHABORT ON when you create the trigger, because it is the setting at run-time that matters.
I thought for a while that the database option ARITHABORT could do it, but my testing indicates that it will not.
It seems that we should not even talk about changing the application; this can not be controlled by connection strings.
But don't give up! There is a server-configuration option to pre-set user options, and my testing indicates that it does indeed have effect:
EXEC sp_configure 'user options', 64
RECONFIGURE
Before you run the above, check the current value of user options. This is a bit mask, so it if is non-zero, add 64 to the current value.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 25, 2019 at 8:42 pm
You can add SET ARITHABORT ON to the trigger, but it may not help, because the error may be raised when the trigger is compiled the first time.
I don't understand this. How could that happen? A SET within a trigger applies only to that trigger, so if all the SETtings are correctly set as required, how would a compile ( / execution) fail?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 25, 2019 at 8:57 pm
Because at the time the trigger is compiled, ARITHABORT is still ON. The SET command is not a declaration, but an executable statement.
Here is a demo. The database has to be in compat level 80 for the demo to work. If you don't have SQL 2008 around, just trade ARITHABORT with ANSI_WARNINGS. The effect will be the same.
SET ARITHABORT ON
go
CREATE TABLE alpha (a int NOT NULL, b int NULL)
CREATE INDEX b_ux ON alpha(b) WHERE b IS NOT NULL
CREATE TABLE beta (a int NOT NULL)
go
CREATE TRIGGER beta_tri ON beta FOR INSERT AS
SET ARITHABORT ON
INSERT alpha(a, b) VALUES(28, 23)
go
SET ARITHABORT OFF
go
INSERT beta(a) VALUES (1)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 27, 2019 at 4:24 pm
Erland,
Thanks so much for the detailed specification what will require the mode changes. In my case, it was an INSERT into a table with clustered index, where the table had a computed column. As I had the freedom to change this new table I had created, I dropped the computed column and just made it a regular column and provided the computation as an expression within the trigger's INSERT. This should solve the problem. I'll probably know within a week or so...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply