CODEWITHSUNDEEP
mysql
abc
abc

Reputation: 11949

mysql: Insert only if certain conditions are respected

I'm trying to find a way to check ,before adding a new tuple in a table, if the tuple respect some condition and in case of one of the conditions is not respected do not allow the insert.

I've thought of something like

    DELIMITER //
    CREATE  TRIGGER t BEFORE INSERT ON Table
    FOR EACH ROW 
    CALL CHECK1(…);
    CALL CHECK2(…);
    CALL CHECK3(…);
    //
    DELIMITER;

Where check1,check2,check3 are procedures that raise an exception if the NEW.(attributes) that I pass do not respect condition in the inserting table and/or with other tables.

Upvotes: 2

Views: 451

Answers (2)

abc
abc

Reputation: 11949

I'm answering to reply(with a comment my answer would be incomprehensible) and to give more details:

I've used 2 strategies to make my goal, here 2 examples

1)if the check is easy

    DELIMITER $$                                                                                                               
    create trigger RV5_1 before insert on Customer                                     
        for each row begin  
    IF(DATEDIFF(CURDATE(),NEW.birthdate)/365<18)                                                                                                                                                                                     
    THEN                                                           
        SIGNAL sqlstate '45006' set message_text = "too young to be a customer";                       
    END IF;                                                    
    END;                                                               
    $$                                                             
    DELIMITER ;

2) if the check is not easy and need cursors, variables etc

     DELIMITER $$                                                                                                              
     create trigger T2 before insert on Table                            
        for each row begin                                             
     IF (check1(NEW.[_some_attribute/s_]) or
         check2(NEW.[_some_attribute/s_]))                   
     THEN                                                      
          SIGNAL sqlstate '45002' set message_text = "invalid insert";
     END IF;    
     END;                                                    
     $$;                                                     
     DELIMITER ;

where check1 and check2 are stored functions that returns 0 if it's ok or 1 if there are problem with the new tuple.

Maybe someone with the same problem will found this helpful.

Upvotes: 0

GregD
GregD

Reputation: 2877

The best way to do it, is to do the data validation using stored procedures, instead of triggers. The trigger strategy is useful if you only want to filter incoming data. If the objective is to cancel an operation entirely when data values are unsuitable, you cannot do this in MySQL using a trigger.

Upvotes: 1

Related Questions