Prev: SQL 2005: Renamed 'sa' account
Next: list of services
From: RogBaker on 7 Apr 2008 11:13
> Were you able to get yourtriggerworking, by the way?
>
> --
> Erland Sommarskog,SQLServerMVP, esq...(a)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

From: Erland Sommarskog on 7 Apr 2008 17:49
(RogBaker(a)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, esquel(a)sommarskog.se

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
 | 
Pages: 1
Prev: SQL 2005: Renamed 'sa' account
Next: list of services