September 13, 2019, 6:55pm
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:
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.
September 13, 2019, 9:45pm
How are these log tables being created?
September 14, 2019, 5:24am
IdDataLog int identity(1,1) primary Key,
September 14, 2019, 10:35am
Who creates these log tables? Are they created manually or automated fashion
September 14, 2019, 11:26am
It was already manual created by past developer. I've found It already in place.
September 15, 2019, 4:11pm
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?
September 15, 2019, 5:52pm
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?
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
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.