RESOLVED Help! Creating Triggers in SPROCS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

RESOLVED Help! Creating Triggers in SPROCS

Author

Topic

ACushen
Starting Member

29 Posts

Posted - 2007-05-13 : 23:30:23

Hi all-

I have code that creates a simple Trigger that runs whenever a record is INSERTed into a table; if a certain condition is false, it grabs one field of the new record and INSERTs it into a second table. [I know there are better ways to do what I'm doing, but I can't change the table structure, so I need this Trigger.]

During testing, for instant emptying of a table that has several million test records in it, we're using an SPROC that truncates, drops, then re-creates the table that the Trigger runs on. As a result, the Trigger gets dropped as well.
I want the Trigger to also be re-created when the Table gets re-created. However, whenever I try to include code in the drop/re-create SPROC to also re-create the Trigger, I get an error on running the SPROC. I've had to save the code that creates the Trigger as a plain .SQL file, and run it manually each time, after I've already run the SPROC that re-creates the Table.

So my question is this -- can this type of Trigger be created in an SPROC, one that creates a Table? I get errors when I include the code below in an SPROC.

Below is the gist of the trigger, with names changed to protect the guilty:


USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trgInsertData]
ON [dbo].[TableOne]
FOR Insert

AS
INSERT INTO [MyDatabase].[dbo].[TableTwo](FieldOne)
	
SELECT DISTINCT FieldToGrab
FROM [dbo].[TableOne] 
WHERE (flgSomeCondition = 0) AND (FieldToGrab NOT IN (SELECT FieldOne FROM [dbo].[TableTwo]))

Can this Trigger-creation code be run from an SPROC, or not?

There is nothing too fancy in the SPROC that kills and re-creates the table. It truncates, drops, then Creates the Table. I used the "Script Table as" command to create the SPROC, and the SPROC works flawlessly in every other way. But when I try to drop in the above code at the end, after the table has been completely re-created, I get errors when I try to run it!

Seems like I should be able to do this, right? If not, is there another way to automate creation of the Trigger in the SPROC that re-creates the table?

Thanks for any input...

-Andrew

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-05-13 : 23:46:28

Use dynamic SQL to create the trigger.

CODO ERGO SUM

ACushen
Starting Member

29 Posts

Posted - 2007-05-13 : 23:54:49

Ah.

Duh. Of course...

Thanks Michael!

-Andrew

Kristen
Test

22859 Posts

Posted - 2007-05-14 : 02:23:33

You are "processing" the whole of TableOne in your trigger, it would be much more efficient to use the [inserted] table in your Trigger instead.


CREATE TRIGGER [dbo].[trgInsertData]
ON [dbo].[TableOne]
FOR Insert

AS
INSERT INTO [MyDatabase].[dbo].[TableTwo](FieldOne)
SELECT DISTINCT FieldToGrab
FROM inserted
WHERE (flgSomeCondition = 0) 
       AND NOT EXISTS (SELECT * FROM  [dbo].[TableTwo] WHERE FieldOne = FieldToGrab)

(and I recommend NOT EXISTS in this situation as it will be somewhat more efficient that IN)

Kristen

ACushen
Starting Member

29 Posts

Posted - 2007-05-14 : 11:09:17

Thanks Kristen, I'll have a look at what you posted...

-Andrew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources