Re: help with DDL trigger. Moved from other newsgroup.

(RogBaker@xxxxxxxxx) writes:
I wrote a DDL trigger that attempts to alter the table as soon as it
created, allowing all the data to be loaded.
However, something about this trigger causes a prior table "tblA" to
fail.

Here is the error message that I get on inserting into tblA with the
trigger for tblB in place:

Execution of this SQL statement failed: Create table tblA(STATUS
CHAR(1) NOT NULL DEFAULT'', SCHOOLNUM
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT failed because
the following SET options have incorrect settings:
'CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that
SET options are correct for use with indexed views and/or indexes o

You get this error message, because your trigger uses XQuery, when
you use XQuery, these settings must be on: ANSI_NULLS, QUOTED_IDENTIFIER,
CONCAT_NULL_YIELDS_NULL, ANSI_WARNING and ANSI_PADDING. And
NUMERIC_ROUNDABORT must be off.

The first two settings are saved with the SQL module, so if you created
your trigger with ANSI_NULLS and QUOTED_IDENTIFIER, you are safe on
those two.

When you create a table ANSI_PADDING is saved with the table column,
but I don't think this is an issue. At least I hope, because in such
case you are in trouble.

That leads to that the three settings you are having problem with are
set by at run-time by the application, which apparently is an old
one. You should be able to get things to work by putting:

SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON

inside your trigger.




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: NEED TO CALL AS400 S.P
    ... That is to use a trigger on a table and perform an INSERT/UPDATE which will execute the trigger. ... What an error message! ... > what your OLE DB provider is feed the AS400 with? ... > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se ...
    (microsoft.public.data.oledb)
  • Re: Records not inserting...
    ... > I am totally new to SQL server. ... However, in a trigger, would I be ... test the effects of certain data modifications and to set conditions for ... UPDATE statements. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Help for an Oracle Newbie
    ... application I wrote using SQL Server ported over to Oracle. ... Obviously there are several built in data types in SQL Server that do ... Of course I had to use a different name for the trigger under each ...
    (comp.databases.oracle.server)
  • Re: How to syncronize two tables?
    ... What is the error message? ... Pro SQL Server 2000 Database Design - ... > want to sync them with trigger. ...
    (microsoft.public.sqlserver.programming)
  • Re: TRIGGER (AFTER INSERT) and Store Procedure ... insert failed !
    ... >into SQL server, the record exist anyway! ... >If you refresh the data you will see the record... ... it gets refreshed after trigger excecution automatically. ... I don't know your monitoring system, but I guess that removing the result ...
    (microsoft.public.sqlserver.programming)
Loading