help with DDL trigger. Moved from other newsgroup.

[RogBaker]

I haven't gotten a response yet, so I moved this from another group. I
have been working on this for 2 days so if anyone has any ideas, I
would be grateful.

I have a 3rd party program that creates and populates tables in my
SQL
Server 2005 database.

The program fails on the inserts on "tblB" because the field it
creates is too small for the data that it is trying to put in it
(stupid).

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

(yes, it truncates the error message)

My trigger is basically:
USE [IGPLINK]
GO
/****** Object: DdlTrigger [NO_SOUP_FOR_YOU] Script Date:
03/24/2008 16:04:42 ******/
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectName)'))
IF @tableName ='tblB'
BEGIN
ALTER TABLE dbo.tblB ALTER COLUMN STULINK Numeric(16,0)
END

However, when I have enterprise manager script my trigger, it looks
altered. I think these ON/OFF settings at the end are screwing things
up. Any suggestions?

USE [IGPLINK]
GO
/****** Object: DdlTrigger [NO_SOUP_FOR_YOU] Script Date:
03/25/2008 11:10:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectName)'))
IF @tableName ='tblB'
BEGIN
ALTER TABLE dbo.tblB ALTER COLUMN STULINK Numeric(16,0)
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE

[Erland Sommarskog]

(Ro******@gmail.com) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

(Ro******@gmail.com) writes:

Thanks Erland, but that does not seem to work. If I add those in my
trigger script, right before the CREATE TRIGGER statement, then I get
the error on creating the first table, but the trigger effect is in
place. If I put your statements underneath, as in:
...
CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
Go

Now you have a very short trigger that consists of one single statement.
GO terminates the batch, and thus the trigger definition. Remove all those
GO, and you should be fine.

PS, I see you are from Sweden. Have you heard of the Ghost Rider?

In a Swedish context? Doesn't really ring a bell.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[RogBaker]

PS, I see you are from Sweden. Have you heard of the Ghost Rider?

>
In a Swedish context? Doesn't really ring a bell.

He is some guy in Sweden that rides his motorcycle at insanely high
speeds through heavy traffic and makes videos of his dangerous antics.

[Erland Sommarskog]

(Ro******@gmail.com) writes:

PS, I see you are from Sweden. Have you heard of the Ghost Rider?

In a Swedish context? Doesn't really ring a bell.

He is some guy in Sweden that rides his motorcycle at insanely high
speeds through heavy traffic and makes videos of his dangerous antics.

Horrible person. I hope that I don't get to see him live. Or hear him.
I have never understood why some motorcyclists think that their bike is
better if it makes a lot of noise.

Were you able to get your trigger working, by the way?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[RogBaker]

Were you able to get yourtriggerworking, by the way?

>
--
Erland Sommarskog,SQLServerMVP, [e-mail]

Erland,

Yes, thank you very much for you help. (I just got back from
vacation). Your last recommendation about the GO was the key.

Actually, I now want to enhance my process. If you recall from my
original postings, I have this third party utility that puts their
data from their program into SQL Server. It does this for a bunch of
tables, however, I only need 4 of them, so I would like to skip the
inserts on the other tables to save a whole lot of time. I am
wondering if I can use a DDL trigger to detect the table being
created, then have it create a DML trigger which basically has it
ignore the insert. However, what I came up with does not parse
successfully. It's almost like you are not allowed to do a trigger
within a trigger.

CREATE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
FOR CREATE_TABLE
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET QUOTED_IDENTIFIER ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR(25), @xmlEventData.query('data(/
EVENT_INSTANCE/ObjectName)'))
IF @tableName ='ISTD7291'
BEGIN
CREATE TRIGGER NOINSERT_ISTD7291
ON ISTD7291
INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Do nothing
END
END

[RogBaker]

Thanks, figured it out

[Erland Sommarskog]

(Ro******@gmail.com) writes:

However, what I came up with does not parse
successfully. It's almost like you are not allowed to do a trigger
within a trigger.
....
BEGIN
CREATE TRIGGER NOINSERT_ISTD7291
ON ISTD7291
INSTEAD OF INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Do nothing
END
END

You need to do that part with dynamic SQL, as you appears to have found
out.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx