pb with a trigger

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
philnedPerhaps the trigger was created with SET QUOTED_IDENTIFIER OFF. This
setting is remembered for subsequent trigger executions. Try:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
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
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"philned" <philned@.wanadoo.fr> wrote in message
news:418783e6$0$9305$636a15ce@.news.free.fr...
> 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
> philnedsql

pb with a trigger

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
philnedPerhaps the trigger was created with SET QUOTED_IDENTIFIER OFF. This
setting is remembered for subsequent trigger executions. Try:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
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
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"philned" <philned@.wanadoo.fr> wrote in message
news:418783e6$0$9305$636a15ce@.news.free.fr...
> 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

pb with a trigger

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
Perhaps the trigger was created with SET QUOTED_IDENTIFIER OFF. This
setting is remembered for subsequent trigger executions. Try:
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
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
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"philned" <philned@.wanadoo.fr> wrote in message
news:418783e6$0$9305$636a15ce@.news.free.fr...
> 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

pb with a trigger

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[posted and mailed, please reply in news]

philned (philned@.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, esquel@.sommarskog.se

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

pb with "QUOTED_IDENTIFIER"

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
Why are you setting quoted identifiers on? You don't seem to need it.
Same goes for most of the set statements.
"philned" wrote:

> 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
>

passing tablename to proc from trigger

------------------------

I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.

Can it be done ?

trigger x on table y
for update
as
declare @.table_name
select @.table_name = get underlying table_name 'y' from somewhere
exec stored proc (@.table_name)

where do I find the name of the table the current trigger is defined on ?

thanksI want to make a birthday cake out of nitroglycerin.
Sometimes the question is not "can it be done", but "should it be done".
Don't put this kind of crap in a trigger.|||Why don't you tell us what your trying to acccomplish instead of trying to force a technical solution that doesn't seem to be a "best practice" kind of thing

Blind dude, did they base rat on you?|||Huh? Rat? Base? Huh?