Mysql – thesql trigger to trigger only if record doesn’t exist

MySQLphpmyadmintrigger

The following trigger :

DELIMITER //
CREATE TRIGGER auo_mtltp  AFTER UPDATE ON master_table_linfomasltp2016
FOR EACH ROW
BEGIN
            IF (NEW.IMHQ_solicitado = 'SI')
            THEN 
            INSERT INTO imhq (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad,n_marcadores_solicitados)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad,OLD.n_marcadores_solicitados);
            END IF;

            IF (NEW.FISH_solicitado = 'SI')
            THEN
            INSERT INTO fish (ID_CASO,Tipo_de_estudio,Año,Codigo_interno,Iniciales_Px,Sexo,Edad)
            VALUES (OLD.ID_CASO, OLD.Tipo_de_estudio, OLD.Año, OLD.Codigo_Interno, OLD.Iniciales_Px, OLD.Sexo, OLD.Edad);
            END IF;
    END//
DELIMITER ;

Adds a new row into a table if x condition is met. How ever, ID_CASO is a unique index into the child table, and like this, if i update a record already into the child table, it gives me an error, since there is another trigger running that adds the record to the child table if the x condition is met since insertion.

I need this trigger to run only IF the ID_CASO value is not present in the child table.

Any ideas?

I tried IF EXISTS syntax, but no succes so far.

Also, I thought that if you clarified the "NEW." prefix into the row identifier it would only fire the trigger if the value is NEW, but if the value is OLD and there is no NEW value being updated, it uses the OLD one and tries to add the row into the child table. As in, it just runs the trigger again, even if the value was OLD and not NEW.

Best Answer

Change the inserts to

INSERT IGNORE ...

That should avoid the problem as I read it. If I misread, please clarify.

Related Solutions

Mysql – How to structure IF condition in MySQL trigger

I assume you will value TableB first as it contains the order_no. In that case you need to use an update statement in your trigger instead of an insert statement:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER trigger_name
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');

(The GO in this example is used as a batch separator and not send to MySQL.)

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      BBB |
|        3 |      333 |   (null) |

If you also want to react to updates, just create an AFTER UPDATE trigger like this:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER TableA_AfterInsert
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');
GO
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
GO
CREATE TRIGGER TableA_AfterUpdate
    AFTER UPDATE ON TableA
    FOR EACH ROW BEGIN

    IF (OLD.sku != NEW.sku)
    THEN
      UPDATE TableB
      SET sku_copy = NEW.sku
      WHERE order_id = NEW.order_id;
    END IF;
END;
GO
UPDATE TableA
  SET sku = 'NEW'
WHERE order_id = 2;
GO

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      NEW |
|        3 |      333 |   (null) |

In both cases the NEW and OLD virtual tables refer to the table the trigger is defined on. NEW contains the new version of the row that was inserted or changed. OLD contains the pre-change version of the row. OLD is only defined in an update trigger as there is no old version on an insert.

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/

Best Answer

Related Solutions

Mysql – How to structure IF condition in MySQL trigger

How to commit transaction on an after update event trigger

Related Question