Mysql Automatically Change Data

Question

Good afternoon, I wanted to implement a system that would make my database automatically, change the value of one of the fields of an element when the expiration date field was higher than the current system date. I have tried to implement event ataraves, but wanted through Trigger, can you help me? Thank you

Answer 1

Suppose we have a database with a table that stores the employee data of the company. If by chance someone makes a modification to this table, and specifically changes an employee’s surname, we will be able to audit it through another table called an employe_audit. Who will fill the table employed_audit is our example Trigger that will be executed before any UPDATE in the employees table.

== Creating the tables that will be used in this example ==

employee table

CREATE TABLE `empregados` (
      `id_empregado` int(11) NOT NULL,
      `sobrenome` varchar(50) NOT NULL,
      `nome` varchar(50) NOT NULL,
      `email` varchar(100) NOT NULL,
      `cargo` varchar(50) NOT NULL,
      PRIMARY KEY  (`id_empregado`)
)

table employed_audit

CREATE TABLE empregados_auditoria ( 
    id int(11) NOT NULL AUTO_INCREMENT, 
    id_empregado int(11) NOT NULL, 
    sobrenome varchar(50) NOT NULL,
    modificadoem datetime DEFAULT NULL, 
    acao varchar(50) DEFAULT NULL, 
    PRIMARY KEY (id) 
)

== Creating our Example Trigger ==

DELIMITER $$

CREATE TRIGGER antesDeUpdate_empregados 
BEFORE UPDATE ON empregados
FOR EACH ROW BEGIN
INSERT INTO empregados_auditoria
SET acao = 'update',
id_empregado = OLD.id_empregado,
sobrenome = OLD.sobrenome,
modificadoem = NOW(); END$$
DELIMITER ;

== Testing the operation of our sample trigger ==

Since our Rigger is called only when an UPDATE is done on the employee table, and this table is currently empty, we need to include at least one record in it:

INSERT INTO `empregados` (`id_empregado`, `sobrenome`, `nome`, `email`, `cargo`) VALUES ('1', 'Silva', 'Ciclano', '[email protected]', 'Programador');

Now we need to run an UPDATE that modifies an employee’s surname so that Trigger will be executed beforeDeUpdate_employees:

UPDATE empregados SET sobrenome = 'Santana' WHERE id_empregado = 1;

If we run a SELECT in the employee table we can see the employee name we register and then change:

SELECT * FROM empregados;

To be sure that Trigger was successfully executed, we just need to make a query in the table employed_audit and we will see that Trigger has taken charge of inserting a record in this table automatically:

SELECT * FROM empregados_auditoria;

You can get an idea like that.

Answer 2

Are you sure you want a Rigger for this? I think it’s safer for you to do something like

CREATE TABLE anuncios (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data_expiracao DATETIME NOT NULL,
    -- outros campos…
    -- (mas não o campo de “estado” do anúncio)
);

CREATE VIEW anuncios_estado AS
SELECT
    id,
    CASE
        WHEN data_expiracao < CURRENT_TIMESTAMP THEN 'expirado'
        -- outros casos vão aqui
    END AS estado
FROM anuncios;
     -- INNER JOIN outras tabelas auxiliares

Of course you’d put it there in the view anuncios_estado the logic to decide, for example, whether an ad is active (looking, for example, at another table if the ad payment was made, or something like that). Then at the time of consulting you would have to do something like

FROM anuncios INNER JOIN anuncios_estado USING (id)

to pull the state field.

The advantage of doing it this way is that you don’t have to think of all the cases where the data you will query can be changed - the day you create an ad refund system, this system will automatically kill the ads when the payment is reversed, without the risk if you have an ad in an inconsistent state; to desadvantage is that the query becomes more expensive, but 1) this can be mitigated with the proper use of indexes; 2) you still do not know if this will indeed be a bottleneck; 3) your client still does not know in fact what determines the status of the ad, and dynamically generating the state gives you more flexibility to change.

The day you’re getting beat up from the performance, you can just do

CREATE TABLE anuncios_estado_precomputado (…)

And then every day, or every hour, you put a script to run that does

BEGIN;
DELETE FROM anuncios_estado_precomputado;
INSERT INTO anuncios_estado_precomputado
SELECT FROM anuncios_estado;
COMMIT;

(or something like make a Join of the precomputada version with the view, to find the different lines and update only them, if that’s probably faster)

You can, on this day, also implement a set of triggers to update advertisement_state_precomputado without waiting for this job to run, but at the time when estado is an explicit column of your template, you will need the view - even if you decide to do everything with triggers, one day you or a user vain err, and you going need to audit which ads are not in the state they should be in.

---

Or, the most pragmatic argument to make with the view: Sgbds are not cron; they do not generate events (and therefore cannot trigger triggers) just by passing time. To do this you want, you would have to have a cronjob issuing the appropriate Updates running e.g. from minute to minute, or the frequency your DBMS would handle processing these expirations; the view does not suffer from these limitations and always returns the status updated in real time.