pb with a trigger

[philned]

hello,

I've a problem with a trigger :
this trigger update a file with an index field but it's return an error
: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
parameters

for information, this is my trigger :

CREATE TRIGGER COOP_article ON F_ARTICLE FOR insert AS
SET NOCOUNT ON
set arithabort on
set concat_null_yields_null on
set quoted_identifier on
set ansi_nulls on
set ansi_padding on
set ansi_warnings on
set numeric_roundabort off
declare @code varchar(17)
select @code=ar_ref from inserted
begin
*/ replace if reference = 0 */
if @code='0'
begin

update f_article set ar_ref='test' from f_article join
inserted on f_article.cbmarq=inserted.cbmarq
end
end
if you ave an idea...
thanks
philned

[Erland Sommarskog]

[posted and mailed, please reply in news]

philned (ph*****@wanadoo.fr) writes:

I've a problem with a trigger :
this trigger update a file with an index field but it's return an error
: UPDATE stop because the Set option : "QUOTED_IDENTIFIER" return bad
parameters

This is because there is an indexed view or an indexed computed
column some where.

Putting all those SET commands in the trigger is not a good idea, because
it may cause recompilations which can be expensive performancewise.

Instead make sure that you issue a SET ARITHABORT ON when you connect.
You can also set this default for your database, or even your setver.

As for QUOTED_IDENTIFIER, this setting is a bit special. It doesn't
help if you set it in the trigger, because it is the setting of
QUOTED_IDENTIFER when the trigger was created that applies. This also
applies to the setting ANSI_NULLS.

There are some tools that unfortunately turn of QUOTED_IDENTIFIER by
default. The command-line tool OSQL is one of them. Use -I with OSQL
to have QUOTED_IDENTIFIER on. The other is Enterprise Manger, but you
should not use EM for editing SQL Code, use Query Analyzer instead.

In short: you need to reload the trigger with QUOTED_IDENTIIER on.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp