SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Trigger to Truncate then Insert rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlLOVER1234
Starting Member

1 Posts

Posted - 10/08/2013 :  17:46:43  Show Profile  Reply with Quote
Everyday, some rows will be inserted into a SQL Server Table (T_PAST). These rows will be records from the past (ie August 1, 2013) as well as records that are in the future (ie January 1, 2014). I want to leave the past dated records in the table (T_PAST), but for the future date records, I would like to: 1) Delete them from the original table 2) Insert them into a new table which only has future dated records (T_FUTURE)

The thing is, the future dated records can have changes in the columns, so instead of running an update query as well, I would prefer to truncate the T_FUTURE table, and reinsert the records.

Everything works in the sense that the proper records are insert into T_PAST, the proper records are delete from T_PAST and the T_FUTURE table is truncated. My problem is that when I insert multiple future dated records, only the last record shows in the T_FUTURE table, not all of them.


ALTER TRIGGER [dbo].[trg_GetFuture]
ON [dbo].[T_PAST]
AFTER INSERT
AS

BEGIN
TRUNCATE TABLE dbo.T_FUTURE
END

BEGIN
INSERT INTO dbo.T_FUTURE
SELECT *
FROM INSERTED
WHERE DATE > GETDATE()
END

BEGIN
DELETE FROM dbo.T_PAST
WHERE DATE > GETDATE()
END


Thanks!!

James K
Flowing Fount of Yak Knowledge

3649 Posts

Posted - 10/08/2013 :  17:54:20  Show Profile  Reply with Quote
This sounds like you have multiple insert statements that insert one record at a time. The trigger will be called once for each record, so each time you will be truncating the T_FUTURE table and inserting that one record - which effectively would mean that you end up with the very last record.

So you have to do one of two things:

a) Amend your insert statement so it collects all the records that need to be inserted and inserts them in a single insert statement.

b) Amend the trigger so you don't truncate the table - instead you add the record to the T_FUTURE table.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000