Bidirectional trigger

Question

Bidirectional trigger

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I have to make two triggers, one that, after inserting into test1, insert into test2. So far so good, the problem I have is that I should do the same in the inverse case. When there is an insert in test2, I must do the same insert in test1. I understand that the error I have is that I can enter an infinite loop, but I would like to know if there is any possibility of solving this:

DELIMITER $$ CREATE TRIGGER 'ai_test1_test2' AFTER INSERT ON 'test1' 
FOR EACH ROW 
BEGIN INSERT INTO test2 (vtest2) VALUES (new.vtest1)
END$$
DELIMITER ;

In the bidirectional insert it would be:

DELIMITER $$ CREATE TRIGGER 'ai_test2_test1' AFTER INSERT ON 'test2' 
FOR EACH ROW 
BEGIN INSERT INTO test1 (vtest1) VALUES (new.vtest2)
END$$
DELIMITER ;

Does anyone know how I could solve it? Thanks in advance.

mysql sql

asked by dankkomcg 05.06.2017 в 11:32

source

2 answers

0

What you need to do is not create a trigger but a procedimiento almacenado and instead of doing the INSERT direct to those tables, execute the procedure with the parameters you need.

DELIMITER $$
CREATE PROCEDURE Replica_Tabla2 (param1 VARCHAR(10), ..., paramN INT)
BEGIN
    INSERT INTO test2 (vtest2) VALUES (param1, ...);
END
$$

and you call it with:

CALL Replica_Tabla2 ('ID', ..., 5);

Here are some notes: Stored Procedures and Functions .

answered by 05.06.2017 в 16:19

I want to create an object within another, but I can not use adds in sprite - HaxeFlixel First PHP associative array key does not work

score 0 · Accepted Answer

I have found a solution to the problem, maybe it is not the most efficient but I can get out of the triggers loop.

It is based on the common identification for the same tuple in the two tables, so by its primary key it could see if they exist in the other table, if there is no create, if it exists, it does not perform any action and leaves the trigger.

DELIMITER $$ CREATE TRIGGER 'ai_test1_test2' AFTER INSERT ON 'test1' 
FOR EACH ROW 
BEGIN 
  IF NOT EXISTS( SELECT vtest2 FROM test2 WHERE vtest2 = new.vtest1 ) THEN
    INSERT INTO test2 (vtest2) VALUES (new.vtest1)
  END IF;
END$$
DELIMITER ;

DELIMITER $$ CREATE TRIGGER 'ai_test2_test1' AFTER INSERT ON 'test1' 
FOR EACH ROW 
BEGIN 
  IF NOT EXISTS( SELECT vtest1 FROM test1 WHERE vtest1 = new.vtest2 ) THEN
    INSERT INTO test1 (vtest1) VALUES (new.vtest2)
  END IF;
END$$
DELIMITER ;