Problem with actual year as a variable in a table name in a trigger

Question

Problem with actual year as a variable in a table name in a trigger

Karl-452938

Valued Member

Points: 71
More actions
December 29, 2010 at 5:21 am

#231509

Hi everybody.
I am trying to write a trigger, that copies data from one incoming table to different archive tables dependening on year and some other conditions.
The trigger should copy at first stage everything from 2010 to a table called Statistics 2010001001 and everything from 2011 to a table called Statistics 2011001001.
I tried the Following (a bit shortened for better understanding):
ALTER TRIGGER [dbo].[Trigger_Copy]
ON [dbo].[Statistics_add]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
declare @year varchar(50)
set @year = datepart(year,getdate())
declare @command varchar(MAX)
set @command = 'Insert Statistics'+ @year + '001001(
DateTimeVisited,
UniqueID,
SessionID)
SELECT
DateTimeVisited,
UniqueID,
SessionID
FROM
Inserted
with (ROWLOCK)
DELETE Statistics_add
FROM Inserted
WHERE Statistics_add.ID = Inserted.ID'
END
This does not have any effect. Does the "command" not work in a trigger or what did I do wrong? Any other suggestions to get this job done?
Thank you for your help...

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

skt5000 SSC-Addicted Points: 479 More actions · Answer 1

I don't think the trigger is the best way to go here, but anyway, you are not excuting your @Command, you are only setting it to something. You'd need to do EXEC (@command). Also, your dynamic sql will not know the table "inserted". Can't this functionality bee done outside of a trigger, like in a job?

Jim

sturner SSC-Insane Points: 22411 More actions · Answer 2

You have several issues with that trigger. The inserted table is known only to the trigger - plus you have multiple commands in the your dynamic SQL statement, you need to separate them with a semi-colon. Plus, I don't even see the statement being executed in your trigger.

Can you do something like this in your trigger:

insert Statistics2010001001

select date, .. ,, etc.

from inserted where Date > '20100101' and date < '20110101'

insert Statistics2011001001

select date, .. ,, etc.

from inserted where Date > '20110101' and date < '20120101'

The probability of survival is inversely proportional to the angle of arrival.

Karl-452938 Valued Member Points: 71 More actions · Answer 3

Thanks for your quick reply.

Since this is a shortened version of the trigger, the insert-statement is a lot longer and I have 10 different tables to write in. This makes together a lot of code with possible performance issues (the tables to write into can have 1 mio. or more entries).

I thought, it might be a better idea to use the "variable-in-the-tablename-version" instead of repeating the same long insert statement 10 times.

If I understand you right , you think that a longer code would be better in this case?

WayneS SSC Guru Points: 95460 More actions · Answer 4

Sounds like a partitioned view here will handle things - see this link for how to handle this. The trigger will insert into the view, which will properly put the data into the proper table.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2