Trigger "Triggers" Insert OR Update Mysql

Question

Trigger "Triggers" Insert OR Update Mysql

Navigation

#1 by (4 votes)

2

My question concerns the creation of triggers in Mysql

Currently create the following trigger:

CREATE TRIGGER movimientotEquipo AFTER INSERT ON movimiento
 FOR EACH ROW
 IF EXISTS (SELECT * FROM equipo WHERE equipo.ide_af = NEW.af_ide) THEN

    UPDATE equipo  
     SET mov_ide = NEW.mov_ide, 
      est_equi = NEW.hard_estado
     WHERE equipo.ide_af = NEW.af_ide
ELSE 

INSERT INTO equipo ('ide_af','hard_ide','ide_ing','mov_ide','man_ide','est_equi') 
VALUES (NEW.ide_af , NEW.hard_ide, '0' , NEW.mov_ide, '0', NEW.esta_ide );

END IF;

But it generates error in IF

If I only try to create the Insert it works ...

Any suggestions in MYSQL

EDITED

The trigger is created by the console and there is all the code I have tried.

mysql trigger

asked by Andres Felipe Diaz 20.04.2017 в 21:53

source

1 answer

How to clean a field from a textView? Visual Basic - Open an Excel file

score 4 · Accepted Answer

The problem is that in MySQL EXISTS is an operator that can only be used in the WHERE clause of a query and not with the IF statement.

Try the following code:

DELIMITER $$

CREATE TRIGGER movimientotEquipo AFTER INSERT ON movimiento
FOR EACH ROW
BEGIN
    DECLARE vExists INT DEFAULT 0;
    SELECT
        1
    FROM
        DUAL
    WHERE
        EXISTS (SELECT * FROM equipo WHERE equipo.ide_af = NEW.af_ide)
    INTO
        vExists;

    IF vExists = 1 THEN
        UPDATE equipo SET
            mov_ide = NEW.mov_ide, 
            est_equi = NEW.hard_estado
        WHERE
            equipo.ide_af = NEW.af_ide;
    ELSE 
        INSERT INTO equipo (
            'ide_af',
            'hard_ide',
            'ide_ing',
            'mov_ide',
            'man_ide',
            'est_equi'
        ) VALUES (
            NEW.ide_af,
            NEW.hard_ide,
            '0',
            NEW.mov_ide,
            '0',
            NEW.esta_ide
        );
    END IF;
END;$$

DELIMITER ;