Ciupaz
September 13, 2019, 6:55pm
#1
Hello,
I have a table (DataLog) that take log data from a web application.
Data is simply: IdLog - Message - Date
I need to clean this table every week (for example) and save old log entries for every week and save them in other table like:
DataLog_2019_34
DataLog_2019_33
DataLog_2019_32
where 32,33,34 is the week of the year (any other ideas are well accepted).
Is it possible to accomplish it with a trigger?
Thanks a lot in advance.
Luigi
yosiasz
September 13, 2019, 9:45pm
#2
How are these log tables being created?
Ciupaz
September 14, 2019, 5:24am
#3
For example:
IdDataLog int identity(1,1) primary Key,
Message varchar(2000),
LogDate datetime
Luigi
yosiasz
September 14, 2019, 10:35am
#4
Who creates these log tables? Are they created manually or automated fashion
Ciupaz
September 14, 2019, 11:26am
#5
It was already manual created by past developer. I've found It already in place.
yosiasz
September 15, 2019, 4:11pm
#6
You could do it with a trigger or with a SQL Job scheduled to run at an interval (hourly/every 15min). With a trigger it would write out to your archive tables instantly.
Also is there any reason you are creating a new table for each week?
Ciupaz
September 15, 2019, 5:52pm
#7
No problem for creating a table (real) every week. The space is in large amount. But, anyway, how can I write a trigger like this?
Another idea
is create a stored proc
in that stored proc do all your stuff
I need to clean this table every week (for example) and save old log
entries for every week and save
them in other table
create a job ...in that job call stored proc
and schedule the job to run every week
please see link below
hope this helps
1 Like
You wouldn't use a trigger for that. You'd create separate code, such as a stored procedure, that would to the copying/archiving.
Most importantly, the table should not be keyed like this:
Instead , it should be like this:
IdDataLog int IDENTITY(1, 1) NOT NULL,
Message varchar(2000) NOT NULL,
LogDate Datetime NOT NULL,
PRIMARY KEY ( LogDate, IdDataLog )
That will cluster the rows in the LogDate order first, making copying and lookups vastly more efficient.