Trigger to Truncate then Insert rows

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

SQL Server Administration (2005)

Trigger to Truncate then Insert rows

Author

Topic

sqlLOVER1234
Starting Member

1 Post

Posted - 2013-10-08 : 17:46:43

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-08 : 17:54:20

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources