Mysql automatically change data [closed]

Suppose we have a database with a table that stores company employee data. If someone does a change to this table, and specifically change an employee's last name, we will be able to audit this through another table called audit_employees. Who will fill the employee_auditory table is our sample 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')
)

Audit Employees table

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 example Trigger ==

As our trigger is called only when an UPDATE is made in 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 execute an UPDATE that modifies an employee's last name before the beforeUpdate_employed trigger is executed:

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

If we execute a SELECT in the table employees we will be able to see the name of the employee that we register and then we change:

SELECT * FROM empregados;

In order to make sure that the trigger was executed successfully, we just need to query the employee_auditoria table and we will see that the trigger has automatically inserted a record into this table:

SELECT * FROM empregados_auditoria;

You can already have an idea like this.

Are you sure you want a trigger for this? I think it's safer for you to do something like this.

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 would put the logic in%% of the logic to decide, for example, whether an ad is active (looking, for example, at another table if 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 status field.

The advantage of doing this is that you do not have to think about all the cases where the data you are going to query can be changed - the day you create an ad repay system, this system will automatically kill ads at the time the payment is reversed, without the risk if you have an ad in an inconsistent state; the des advantage 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 is in fact going to be a bottleneck; 3) Your customer still does not really know what determines the ad's state, and generating the status dynamically gives you more flexibility to change.

On the day you're catching up on performance, you can simply 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 a join of the precomputed version with the view, to find the different lines and update only the ones, if this is to be faster)

On that day, you can also implement a set of triggers for updating prefix_advertising without having to wait for this job to run, but at the moment anuncios_estado is an explicit column of your model, you will need the view - even if you resolve to do everything with triggers, one day you or a go user will err, and you will need to audit which ads are not in the state they should be.

Or, the most pragmatic argument to do with the view: SGBDs are not estado ; they do not generate events (and therefore can not trigger triggers) just by the passage of time. To do what you want, you would have to have a cronjob by issuing the appropriate UPDATEs by running e.g. from minute to minute, or the frequency that your DBMS would be able to handle those expirations; the view does not suffer from these limitations and always returns the updated status in real time.