Delete record when the date of a column is reached

Question

Delete record when the date of a column is reached

Navigation

#1 by (1 votes)

1

I have the tables:

In%with%Ihavecolumnanunciosthatreceivesthenumberofdaysthatthatadisvalid,Iwanteverydaythatpassesthedatabasetoautomaticallydecreaseexpiracaointhatcolumn,inacountdowntodeactivatetheannouncement.Andwhenitreaches0,itcreatesarecordinthe1tablewithexpiracaoexpiracao.supermercado.Isitpossibletodothiswithroutines?Ihavenevermovedwithroutines,ItriedtosearchbutIdidnotunderstand,Ihopetheydonotclosethequestion.

Withtheanswerfrom@bigownIresearchedandunderstoodabit,Ibootedatestdatabaselikethis:

//Notificationstable,replacestableexpiration - -Tablestructureanuncios.supermecado -

CREATETABLEIFNOTEXISTS'notificacao'('id'int(11)NOTNULLAUTO_INCREMENT,'idteste'int(11)NOTNULL,'mensagem'varchar(20)NOTNULL,PRIMARYKEY('id'))ENGINE=InnoDBDEFAULTCHARSET=latin1AUTO_INCREMENT=1;----------------------------------------------------------

//TestTable,replacetableads

----Estruturadatabela'teste'--CREATETABLEIFNOTEXISTS'teste'('id'int(11)NOTNULLAUTO_INCREMENT,'expiracao'int(11)NOTNULL,'nome'varchar(10)NOTNULL,PRIMARYKEY('id'))ENGINE=InnoDBDEFAULTCHARSET=latin1AUTO_INCREMENT=5;----Extraindodadosdatabela'teste'--INSERTINTO'teste'('id','expiracao','nome')VALUES(1,1,'Teste01'),(2,99,'Teste02'),(3,149,'Teste03'),(4,199,'Teste04');

//Triggerofthetesttable,whenadataisupdated,itlooksatwhethertheexpirycolumnis0,ifitdoes,itdeletestherecordandinsertsadataintotheothertable

----Acionadores'teste'(TRIGGER)--DROPTRIGGERIFEXISTS'geraNotificacao';DELIMITER//CREATETRIGGER'geraNotificacao'BEFOREUPDATEON'teste'FOREACHROWBEGINIFteste.expiracao=0THENINSERTINTOnotificacao('idteste','mensagem')VALUES(teste.id,"Teste apagado!");
DELETE FROM teste WHERE teste.id = OLD.id;
END IF;
END
//
DELIMITER ;

// Event that every 10 seconds decrements 1 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 run script nothing happens, the test table does not decrement the expiration value, and it is also impossible to manually edit the value of it.

mysql sql trigger

asked by anonymous 12.05.2016 / 19:06

1 answer

Form for relationship ForeignKey How do I pull the selection from a dropdownlist using jQuery?

score 1 · Accepted Answer

To get what you want you must create a scheduler. This is done with EVENT that looks like TRIGGERS , but it does take some time.

Scheduler documentation .

With this it executes a UPDATE according to what to move every day (at any time you want).

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

In general terms this is it.