473,558 Members | 2,885 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

help with DDL trigger. Moved from other newsgroup.

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_YI ELDS_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_YIE LDS_NULL ON
GO
SET QUOTED_IDENTIFI ER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDAB ORT 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.q uery('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_IDENTIFI ER 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.q uery('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_IDENTIFI ER OFF
GO
ENABLE TRIGGER [NO_SOUP_FOR_YOU] ON DATABASE
Mar 26 '08 #1
7 3052
(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_YI ELDS_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_IDENTIFI ER,
CONCAT_NULL_YIE LDS_NULL, ANSI_WARNING and ANSI_PADDING. And
NUMERIC_ROUNDAB ORT must be off.

The first two settings are saved with the SQL module, so if you created
your trigger with ANSI_NULLS and QUOTED_IDENTIFI ER, 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_YIE LDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
inside your trigger.


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.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
Mar 26 '08 #2
(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****@sommarsk og.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
Mar 27 '08 #3
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.
Mar 28 '08 #4
(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****@sommarsk og.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
Mar 28 '08 #5
Were you able to get yourtriggerwork ing, by the way?
>
--
Erland Sommarskog,SQLS erverMVP, esq...@sommarsk og.se
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_YIE LDS_NULL ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET QUOTED_IDENTIFI ER ON
DECLARE @xmlEventData XML,
@tableName VARCHAR(50)
SET @xmlEventData = eventdata()
SET @tableName = CONVERT(VARCHAR (25), @xmlEventData.q uery('data(/
EVENT_INSTANCE/ObjectName)'))
IF @tableName ='ISTD7291'
BEGIN
CREATE TRIGGER NOINSERT_ISTD72 91
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

Apr 7 '08 #6
Thanks, figured it out
Apr 7 '08 #7
(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_ISTD72 91
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****@sommarsk og.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
Apr 7 '08 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3522
by: kevin jin | last post by:
Hi, I did read Andrew's article about SQL Server trigger. After a 'Insert' trigger is fired,is there any way we can modify newly added data in virtual 'Insert' table before put them to the database? Thanks! Kevin Jin
9
3449
by: Martin | last post by:
Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8)
1
2208
by: MM | last post by:
I currently have a trigger on a table which works fine. It performs some audit trail functions. ****************************************************** CREATE TRIGGER trg_1 ON .  FOR INSERT, UPDATE, DELETE AS BEGIN  SET NOCOUNT ON
11
5536
by: ricolee99 | last post by:
Hi everyone, I'm trying to invoke my .exe application from a remote server. Here is the code: ManagementClass processClass = new ManagementClass ("\\\\" +"RemoteServerName" + "\\root\\CIMV2:Win32_Process");
3
5255
by: Navin | last post by:
Hello friends... On a single asp.net web page i have a single server-side form that contains a couple of user-controls. Each user control has its own functionality and contains server-side textbox controls, requiredfieldvalidator controls which validate the textboxes, and a linkbutton control that postbacks the web page. The problem is, when...
4
1944
by: SUKRU | last post by:
Hello everybody. Unfortunately I am pretty new to sql-server 2000 I need some help with a Trigger I created. I created a trigger witch takes the id of the affected row and does a update on a other table with that ID. The trigger works fine with one affected row. But when there are more then one rows affected, i get an error. I found out...
3
3711
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code in the application, but I'd rather not! DDL for table and trigger below. TIA
15
2561
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to determine who needs to receive the text message then send the message to the address. Only problem is, the employee may receive up to 4 of the...
11
3098
by: Sven Reuter | last post by:
Hallo NG, ich versuche gerade ein PHP-Script zu bauen, das einen Trigger in einer MySQL-Datenbank erstellt. Doch leider funktioniert das nicht und ich habe auch über google keine Hinweise gefunden. Ich vermute, dass es am Delimiter liegt. Der Code, der per PHP abgeschickt wird:
0
7549
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7835
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8061
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7593
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6183
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3602
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3578
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1164
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
869
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.