Create a trigger to execute it before insert

Question

Create a trigger to execute it before insert

Navigation

#1 by (2 votes)

1

I want to do a Trigger in MySQL that does the following:

Background: I have a table called x with 3 columns: id (int), name (varchar) and state (enum). There can only be 1 record with the status Yes (column type enum; Yes, No)

When inserting a record in table x, the Trigger will look inside table x if there already exists a record with status Yes and then change it to No.

I currently have this:

CREATE TRIGGER test BEFORE INSERT ON x
FOR EACH ROW BEGIN

/*
    Aquí ya no se como decirle que busque
    a los que están en estatus Si
    y los cambie por No
*/

mysql mysqli

asked by Alfonso Carrasco 03.06.2016 в 23:37

source

1 answer

Error updating Mouse zoom over event that shows an iframe

score 2 · Accepted Answer

A trigger can not modify the same table to which it is linked.

The simplest solution I can think of is to write a procedure to insert values that makes the necessary update:

create table x (
  id int unsigned not null auto_increment primary key,
  nombre varchar(50),
  estado enum('Si','No')
);

delimiter //
create procedure insertaEnX(n varchar(50), e char(2))
begin
  if e = 'Si' then
    update x
      set estado = 'No';
  end if;
  insert into x(nombre, estado) values(n, e);
end //
delimiter ;

Example of use:

call insertaEnX('Nombre 1', 'Si');
select * from x;
    | id |   nombre | estado |
    |----|----------|--------|
    |  1 | Nombre 1 |     Si |

call insertaEnX('Nombre 2', 'No');
select * from x;
    | id |   nombre | estado |
    |----|----------|--------|
    |  1 | Nombre 1 |     Si |
    |  2 | Nombre 2 |     No |

call insertaEnX('Nombre 3', 'Si');
select * from x;
    | id |   nombre | estado |
    |----|----------|--------|
    |  1 | Nombre 1 |     No |
    |  2 | Nombre 2 |     No |
    |  3 | Nombre 3 |     Si |