Trigger to save and move data log

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

How are these log tables being created?

For example:

IdDataLog int identity(1,1) primary Key,
Message varchar(2000),
LogDate datetime

Luigi

Who creates these log tables? Are they created manually or automated fashion

It was already manual created by past developer. I've found It already in place.

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?

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 :slight_smile: :slight_smile:

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.

Thank you Harish.

Thank you Scott.