Trigger Remove after 24 hrs

Question

Trigger Remove after 24 hrs

Navigation

#1 by (1 votes)

0

This is the way to perform a trigger that removes data from my database that is less than 24 hours.

I have the following table of reservations and in this I add a field 'Type' that can be permanent or by hours, if it is by hours it will be eliminated in 24 hours.

CREATE TABLE IF NOT EXISTS 'Inv_Reserva' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'Item' varchar(50) NOT NULL,
  'CodPza' varchar(45) DEFAULT NULL,
  'Tipo' varchar(45) NOT NULL,
  'timestamp' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ('id')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=246 ;

For the date field I use timestamp since I save the date and time. In my trigger I would first ask you to find the data with Tipo Hour "DELETE FROM Inv_Reserva WHERE Tipo='Hora'"

I hope to have explained myself well.

mysql trigger

asked by MoteCL 07.11.2018 в 18:40

source

1 answer

Create service that runs npm start Add CORS to SOAP service with Ionic

score 1 · Accepted Answer

What you need is a EVENTO , not a TRIGGER .

Translated from the MySLQ documentation:

... the scheduled tasks of this type are also sometimes known as "temporary triggers", which implies that these are objects that are activated over time. While this is essentially correct, we prefer to use the term events to avoid confusion with triggers ...

For your case, you can create an event that runs every minute and delete reservations that take more than 24 hours:

CREATE EVENT 'eliminar' 
ON SCHEDULE EVERY 1 MINUTE STARTS '2018-11-07 00:00:00.000000' ENDS '2018-12-31 00:00:00.000000' 
ON COMPLETION NOT PRESERVE ENABLE 
DO 
DELETE FROM Inv_Reserva WHERE Tipo='Hora' AND TIMESTAMPDIFF(HOUR,timestamp,NOW()) > 24