CODEWITHSUNDEEP
sql-servert-sqldrop-table
ShaneLS
ShaneLS

Reputation: 476

Dropping Tables within an AFTER INSERT Trigger

I am looking for suggestions on how to handle incoming data from a C# client app that sends via System.Data.SqlClient.SqlBulkCopy. The original intent was to maintain all data sent via the app into our sql server where the data would be 'massaged'. To keep things secure, data is sent to write only tables where an AFTER INSERT trigger will copy to another table and delete incoming.

Simple Example:

ALTER TRIGGER [dbo].[FromWeb_To_MyTable] ON [dbo].[_FromWeb_MyTable]
AFTER INSERT
AS 
BEGIN
   SET NOCOUNT ON;

   INSERT INTO [dbo].[MyTable]([MyTableID],[MemberID],[LocationID],[ODBCID],[InsertDateTime])
   SELECT i.[MyTableID],i.[MemberID],i.[LocationID],i.[ODBCID],i.[InsertDateTime]
   FROM Inserted i;

   DELETE FROM _FromWeb_MyTable
   WHERE [MyTableID] IN (SELECT i.[MyTableID] FROM Inserted i);
END

Now I am going to be using a bit differently and need to delete everything in the 'go to' table. My largest table will be around 350,000 records. I intend to DROP and re-CREATE said table like so: (this method IS working fine see my questions below)

ALTER TRIGGER [dbo].[FromWeb_To_MyTable] ON [dbo].[_FromWeb_MyTable]
AFTER INSERT
AS 
BEGIN
   SET NOCOUNT ON;

   IF EXISTS(SELECT [name] FROM sys.TABLES WHERE [name] = 'MyTable') DROP TABLE [dbo].[MyTable];
   CREATE TABLE [dbo].[MyTable] (
      [MyTableID] [int] NOT NULL,
      [MemberID] [varchar](6) NOT NULL,
      [LocationID] [varchar](50) NOT NULL,
      [ODBCID] [varchar](50) NOT NULL,
      [InsertDateTime] [datetime] NOT NULL
   ) on [PRIMARY];

   INSERT INTO [dbo].[MyTable] ( [MyTableID], [MemberID],[LocationID],[ODBCID],[InsertDateTime] )
   SELECT i.[MyTableID],i.[MemberID],i.[LocationID],i.[ODBCID],i.[InsertDateTime]
   FROM Inserted i;

   DELETE FROM _FromWeb_MyTable
   WHERE [MyTableID] IN (SELECT [MyTableID] FROM Inserted);
END

Does anyone see any problems with this method? Any different suggestions? Can someone explain when and how to index the newly recreated table?

Upvotes: 0

Views: 946

Answers (1)

peterm
peterm

Reputation: 92785

Since you reuse your table instead of dropping and re-creating it just use TRUNCATE.

ALTER TRIGGER dbo.FromWeb_To_MyTable ON dbo._FromWeb_MyTable
AFTER INSERT
AS 
BEGIN
   SET NOCOUNT ON;

   TRUNCATE TABLE dbo.MyTable;

   INSERT INTO dbo.MyTable (MyTableID, MemberID,LocationID,ODBCID,InsertDateTime)
   SELECT i.MyTableID,i.MemberID,i.LocationID,i.ODBCID,i.InsertDateTime
   FROM Inserted i;

   DELETE FROM _FromWeb_MyTable
   WHERE MyTableID IN (SELECT MyTableID FROM Inserted);
END

Upvotes: 1

Related Questions