0

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!!

user2852426
  • 1
  • 1
  • 3
  • Where do you insert into `T_FUTURE`? Tip: You may want to add `declare @Now as DateTime = GetDate();` near the beginning of the procedure and use `@Now` thereafter. Otherwise you have an opportunity for some hard to find timing bugs as the values of `GetDate()` changes from the `INSERT` to the `DELETE`. – HABO Oct 07 '13 at 00:09
  • Sorry, had a typo - it now shows where I insert into T_FUTURE. Also, thanks for the heads up on the declare. – user2852426 Oct 07 '13 at 23:06

1 Answers1

0

This is because you are truncating the T_FUTURE table in the trigger. Every time trigger is fired it will first truncate the T_FUTURE table and then will insert a new records to this table.

Mandar Patil
  • 538
  • 2
  • 10
  • 29
  • Yeah, I realized that as well. I thought the trigger will fire only after all rows have been inserted, so it would only truncate once, then add all of them, but that is not the case I guess. Any idea as to how to fix this? – user2852426 Oct 07 '13 at 15:20