Delete record when a column date is reached

Question

I have the tables:

In anuncios got the spine expiracao which receives the amount of days that that notice is valid, I want each day that the database automatically decreases 1 in this column, a countdown to disable the ad. And when it reaches 0, let it create a record in the table expiracao with expiracao.supermercado availing anuncios.supermecado. Is it possible to do this with routines? I never messed with routines, I tried to research but I didn’t understand, I hope they don’t close the question.

With the reply of @Maniero I researched and understood a little, I created a test database like this:

// Notifications table, replaces expiration table -- -- Structure of the table notificacao --

CREATE TABLE IF NOT EXISTS `notificacao` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idteste` int(11) NOT NULL,
  `mensagem` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

// Test Table, replaces advertisement table

--
-- Estrutura da tabela `teste`
--

CREATE TABLE IF NOT EXISTS `teste` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expiracao` int(11) NOT NULL,
  `nome` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Extraindo dados da tabela `teste`
--

INSERT INTO `teste` (`id`, `expiracao`, `nome`) VALUES
(1, 1, 'Teste01'),
(2, 99, 'Teste02'),
(3, 149, 'Teste03'),
(4, 199, 'Teste04');

// Test table trigger, when a data is updated looks if the expiration column equals 0, if yes, erases the record and inserts a data into the other table

--
-- Acionadores `teste` (TRIGGER)
--
DROP TRIGGER IF EXISTS `geraNotificacao`;
DELIMITER //
CREATE TRIGGER `geraNotificacao` BEFORE UPDATE ON `teste`
 FOR EACH ROW BEGIN
IF teste.expiracao = 0 THEN
INSERT INTO notificacao (`idteste`, `mensagem`) VALUES (teste.id, "Teste apagado!");
DELETE FROM teste WHERE teste.id = OLD.id;
END IF;
END
//
DELIMITER ;

// Event that every 10 seconds decreases 1 of expiration

DELIMITER $$
--
-- Eventos
--
CREATE DEFINER=`root`@`localhost` EVENT `decrementaDia` 
ON SCHEDULE EVERY 10 SECOND 
STARTS '2016-05-12 15:11:10' ON COMPLETION NOT PRESERVE ENABLE 
DO UPDATE teste SET teste.expiracao = (teste.expiracao - 1) WHERE 1$$
DELIMITER ;

When I execute script nothing happens, the test table does not decrease the expiration value, and it also becomes impossible to manually edit the value of it.

Answer 1

To get what you want you should create a scheduler. This is done with EVENT which is similar to TRIGGERS, but it occurs of time time.

Scheduler documentation.

With that, execute a UPDATE according to what you move every day (at the time you want).

Already the change in the table expiracao can be done with a TRIGGER on the table anuncios where each change will decide whether to fire a INSERT in the other table.

In general terms this is.