CODEWITHSUNDEEP
mysqldatabasedatabase-design
Aristona
Aristona

Reputation: 9351

Is there any built-in alternatives to cron in MySQL or other databases?

It's pretty basic what I'm asking.

Imagine a column for date in users table.

Username --- RegistrationDate ----- Authority
user1     -  (today's date)     -   1
user2     -  (yesterday's date) -   1

Each user has to be upgraded to Authority = 2 after 10 days automatically.

I know I can do this with PHP by executing it constantly with CRON, but what about built-in features of databases?

Something like:

//pseudo code
bind trigger on users.RegistrationDate, function() {
       if(RegistrationDate >= RegistrationDate+10 days)
           return updateAuthority(1)
}

One important thing about this is the performance. It shouldn't check for each trigger in a loop, whatever should be triggered should be triggered right on time by binding them directly on the column.

Second important thing is, it should be exported when I take a backup of the database. I don't want to re-create them all.

I'm not an expert about databases, but I use MySQL for this project and need a similar feature for MySQL. If it doesn't exists and I have to continue using a cron, would you please tell me if similar feature exists in Oracle/MSSQL or PostgreSQL so I can port my database? (oh, and MongoDB too, it may also be an alternative.)

Thank you.

Upvotes: 2

Views: 623

Answers (3)

Federico Razzoli
Federico Razzoli

Reputation: 5391

In MySQL you have events: http://dev.mysql.com/doc/refman/5.5/en/create-event.html

Just create an event which is triggered every day and updates Authority.

EDIT: (to answer a question below)

I'm sure the wizard exports events, but if it doesn't, you can use the mysqldump program (it's in the bin directory). You can also use the SHOW CREATE EVENT command to export a single event: it returns the SQL command you need to re-create the event.

Upvotes: 4

Frank Schmitt
Frank Schmitt

Reputation: 30835

Oracle has dbms_scheduler (dbms_job is deprecated).

Features include

  • custom schedules (e.g. every 5 minutes, every Monday to Friday at 18:00, ...)
  • job chains (dependencies among jobs)
  • disabling / enabling jobs at will
  • running jobs interactively (used mostly for debugging purposes)

Example (run system monitoring every day at 22:00):

 begin
      dbms_scheduler.create_job(
      job_name => 'JOB_SYSTEM_MONITORING'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin my_schema.system_monitoring.checkAndSendEMails; end; '
     ,start_date => to_date('2013-04-29 04:00:00', 'YYYY-MM-DD hh24:mi:ss')
     ,repeat_interval => 'FREQ=DAILY;BYHOUR=22'
     ,enabled => TRUE
     ,comments => 'System monitoring (daily job, runs at 22:00.');
   end;

Upvotes: 0

Oscar
Oscar

Reputation: 13980

Sql Server have the Sql Server Agent. It's a process that run jobs at the specified time. Jobs are formed by steps, and one step can be a Sql Query, or an SSIS package, or executing a program and so on.

http://msdn.microsoft.com/en-us/library/ms189237.aspx

Upvotes: 1

Related Questions