Diferentes tipos de disparadores de MySQL (con ejemplos)

Un disparador de MySQL es un programa almacenado (con consultas) que se ejecuta automáticamente para responder a un evento específico, como la inserción, actualización o eliminación que ocurre en una tabla.

Hay 6 tipos diferentes de disparadores en MySQL:

1. Antes del activador de actualización:
como su nombre lo indica, es un activador que actúa antes de que se invoque una actualización. Si escribimos una declaración de actualización, las acciones del disparador se realizarán antes de que se implemente la actualización.

Ejemplo:
Considerando tablas:

create table customer (acc_no integer primary key, 
                                 cust_name varchar(20), 
                                  avail_balance decimal);
create table mini_statement (acc_no integer, 
                              avail_balance decimal, 
                     foreign key(acc_no) references customer(acc_no) on delete cascade); 

Insertando valores en ellos:

insert into customer values (1000, "Fanny", 7000);
insert into customer values (1001, "Peter", 12000); 

Activar para insertar valores (antiguos) en un registro mini_statement (incluido el número de cuenta y el saldo disponible como parámetros) antes de actualizar cualquier registro en el registro/tabla del cliente:

delimiter //
create trigger update_cus
      -> before update on customer
      -> for each row
      -> begin
      -> insert into mini_statement values (old.acc_no, old.avail_balance);
      -> end; // 

Realización de actualizaciones para invocar el disparador:

delimiter;
update customer set avail_balance = avail_balance + 3000 where acc_no = 1001;
update customer set avail_balance = avail_balance + 3000 where acc_no = 1000; 

Producción:

select *from mini_statement;
+--------+---------------+
| acc_no | avail_balance |
+--------+---------------+
|   1001 |         12000 |
|   1000 |          7000 |
+--------+---------------+
2 rows in set (0.0007 sec) 

2. Disparador posterior a la actualización:
como su nombre lo indica, este disparador se invoca después de que se produce una actualización. (es decir, se implementa después de que se ejecuta una declaración de actualización).

Ejemplo:
Creamos otra tabla:

create table micro_statement (acc_no integer, 
                                  avail_balance decimal, 
            foreign key(acc_no) references customer(acc_no) on delete cascade); 

Inserte otro valor en el cliente:

insert into customer values (1002, "Janitor", 4500);
Query OK, 1 row affected (0.0786 sec) 

Desenstringr para insertar valores (nuevos) de número de cuenta y saldo disponible en el registro micro_statement después de que se haya producido una actualización:

delimiter //
create trigger update_after
       -> after update on customer
       -> for each row
       -> begin
       -> insert into micro_statement values(new.acc_no, new.avail_balance);
       -> end; // 

Haciendo una actualización para invocar el disparador:

delimiter ;
update customer set avail_balance = avail_balance + 1500 where acc_no = 1002; 

Producción:

select *from micro_statement;
+--------+---------------+
| acc_no | avail_balance |
+--------+---------------+
|   1002 |          6000 |
+--------+---------------+
1 row in set (0.0007 sec) 

3. Antes del disparador de inserción:
como su nombre lo indica, este disparador se invoca antes de una inserción o antes de que se ejecute una instrucción de inserción.

Ejemplo:
Considerando tablas:

create table contacts (contact_id INT (11) NOT NULL AUTO_INCREMENT, 
                              last_name VARCHAR (30) NOT NULL, first_name VARCHAR (25),
             ->birthday DATE, created_date DATE, 
                            created_by VARCHAR(30), 
                            CONSTRAINT contacts_pk PRIMARY KEY (contact_id)); 

Activar para insertar información de contacto como nombre, cumpleaños y fecha de creación/usuario en un contacto de tabla antes de que ocurra una inserción:

delimiter //
create trigger contacts_before_insert
            -> before insert
            -> on contacts for each row
            -> begin
            ->    DECLARE vUser varchar(50);
            ->
            ->    -- Find username of person performing INSERT into table
            ->    select USER() into vUser;
            ->
            ->    -- Update create_date field to current system date
            ->    SET NEW.created_date = SYSDATE();
            ->
            ->    -- Update created_by field to the username of the person performing the INSERT
            ->    SET NEW.created_by = vUser;
            -> end; // 

Haciendo una inserción para invocar el disparador:

delimiter;
insert into contacts values (1, "Newton", "Enigma", 
                             str_to_date ("19-08-1999", "%d-%m-%Y"), 
                             str_to_date ("17-03-2018", "%d-%m-%Y"), "xyz"); 

Producción:

select *from contacts;
+------------+-----------+------------+------------+--------------+----------------+
| contact_id | last_name | first_name | birthday   | created_date | created_by     |
+------------+-----------+------------+------------+--------------+----------------+
|          1 | Newton    | Enigma     | 1999-08-19 | 2019-05-11   | root@localhost |
+------------+-----------+------------+------------+--------------+----------------+ 

4. Activador posterior a la inserción:
como su nombre lo indica, este activador se invoca después de que se implementa una inserción.

Ejemplo:
Considere las tablas:

create table contacts (contact_id int (11) NOT NULL AUTO_INCREMENT, 
                              last_name VARCHAR(30) NOT NULL, 
                              first_name VARCHAR(25), birthday DATE,
                              ->CONSTRAINT contacts_pk PRIMARY KEY (contact_id));
create table contacts_audit (contact_id integer, 
                             created_date date, 
                             created_by varchar (30)); 

Desenstringr para insertar contact_id e información de fecha de creación/usuario del contacto en el registro de contactos_auditoría después de que se produzca una inserción:

delimiter //
create trigger contacts_after_insert
            -> after insert
            -> on contacts for each row
            -> begin
            ->    DECLARE vUser varchar(50);
            ->
            ->    -- Find username of person performing the INSERT into table
            ->    SELECT USER() into vUser;
            ->
            ->    -- Insert record into audit table
            ->    INSERT into contacts_audit
            ->    ( contact_id,
            ->      created_date,
            ->      created_by)
            ->    VALUES
            ->    ( NEW.contact_id,
            ->      SYSDATE(),
            ->      vUser );
            -> END; // 

Haciendo una inserción para invocar el disparador:

insert into contacts values (1, "Kumar", "Rupesh", 
                         str_to_date("20-06-1999", "%d-%m-%Y")); 

Producción:

select *from contacts_audit;
+------------+--------------+----------------+
| contact_id | created_date | created_by     |
+------------+--------------+----------------+
|          1 | 2019-05-11   | root@localhost |
+------------+--------------+----------------+
1 row in set (0.0006 sec) 

5. Antes del activador de eliminación:
como su nombre indica, este activador se invoca antes de que se produzca una eliminación o antes de que se implemente la declaración de eliminación.

Ejemplo:
Considere las tablas:

create table contacts (contact_id int (11) NOT NULL AUTO_INCREMENT, 
                             last_name VARCHAR (30) NOT NULL, first_name VARCHAR (25), 
                             birthday DATE, created_date DATE, created_by VARCHAR(30), 
                             CONSTRAINT contacts_pk PRIMARY KEY (contact_id));
create table contacts_audit (contact_id integer, deleted_date date, deleted_by varchar(20)); 

Desenstringr para insertar contact_id e información de usuario/fecha de eliminación de contactos en el registro de auditoría de contactos antes de que se produzca una eliminación:

delimiter //
create trigger contacts_before_delete
            -> before delete
            -> on contacts for each row
            -> begin
            ->
            ->    DECLARE vUser varchar(50);
            ->
            ->    -- Find username of person performing the DELETE into table
            ->    SELECT USER() into vUser;
            ->
            ->    -- Insert record into audit table
            ->    INSERT into contacts_audit
            ->    ( contact_id,
            ->      deleted_date,
            ->      deleted_by)
            ->    VALUES
            ->    ( OLD.contact_id,
            ->      SYSDATE(),
            ->      vUser );
            -> end; // 

Hacer una inserción y luego eliminarla para invocar el disparador:

delimiter;
insert into contacts values (1, "Bond", "Ruskin", 
                             str_to_date ("19-08-1995", "%d-%m-%Y"), 
                             str_to_date ("27-04-2018", "%d-%m-%Y"), "xyz");
delete from contacts where last_name="Bond"; 

Producción:

select *from contacts_audit;
+------------+--------------+----------------+
| contact_id | deleted_date | deleted_by     |
+------------+--------------+----------------+
|          1 | 2019-05-11   | root@localhost |
+------------+--------------+----------------+
1 row in set (0.0007 sec) 

6. Desenstringdor posterior a la eliminación:
como su nombre lo indica, este desenstringdor se invoca después de que se produce una eliminación o después de que se implementa una operación de eliminación.

Ejemplo:
Considere las tablas:

create table contacts (contact_id int (11) NOT NULL AUTO_INCREMENT, 
                            last_name VARCHAR (30) NOT NULL, first_name VARCHAR (25), 
                            birthday DATE, created_date DATE, created_by VARCHAR (30), 
                            CONSTRAINT contacts_pk PRIMARY KEY (contact_id));
create table contacts_audit (contact_id integer, deleted_date date, deleted_by varchar(20));

Desenstringr para insertar contact_id e información de usuario/fecha de eliminación de contacto en el registro de contactos_auditoría después de que se produzca una eliminación:

create trigger contacts_after_delete
           -> after delete
           -> on contacts for each row
           -> begin
           ->
           ->    DECLARE vUser varchar(50);
           ->
           ->    -- Find username of person performing the DELETE into table
           ->    SELECT USER() into vUser;
           ->
           ->    -- Insert record into audit table
           ->    INSERT into contacts_audit
           ->    ( contact_id,
           ->      deleted_date,
           ->      deleted_by)
           ->    VALUES
           ->    ( OLD.contact_id,
           ->      SYSDATE(),
           ->      vUser );
           -> end; // 

Hacer una inserción y eliminarla para invocar el disparador:

delimiter;
insert into contacts values (1, "Newton", "Isaac", 
                             str_to_date ("19-08-1985", "%d-%m-%Y"), 
                             str_to_date ("23-07-2018", "%d-%m-%Y"), "xyz");
delete from contacts where first_name="Isaac"; 

Producción:

select *from contacts_audit;
+------------+--------------+----------------+
| contact_id | deleted_date | deleted_by     |
+------------+--------------+----------------+
|          1 | 2019-05-11   | root@localhost |
+------------+--------------+----------------+
1 row in set (0.0009 sec)