Re: help with DDL trigger. Moved from other newsgroup.
am 07.04.2008 17:13:49 von RogBaker> Were you able to get yourtriggerworking, by the way?
>
> --
> Erland Sommarskog,SQLServerMVP, esq...@sommarskog.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_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