mysql插入前检查记录是否存在的触发器

Question

我试图在插入到一个表之前写一个触发器，而且我还需要检查记录是否存在，那么插入就不应该发生。

DELIMITER $$

DROP trigger IF EXISTS `Before_Insert_igm_vessel_details` $$

CREATE TRIGGER `Before_Insert_igm_vessel_details` BEFORE INSERT ON igm_vessel_details
FOR EACH ROW

BEGIN

  IF (NOT EXISTS(SELECT * FROM igm_vessel_details WHERE VD_MESSAGE_TYPE = NEW.VD_MESSAGE_TYPE and
                VD_CUSTOM_HOUSE_CODE = NEW.VD_CUSTOM_HOUSE_CODE and VD_IGM_NO = NEW.VD_IGM_NO and VD_IGM_DATE = NEW.VD_IGM_DATE and
                VD_IMO_CODE_OF_VESSEL = NEW.VD_IMO_CODE_OF_VESSEL and VD_VESSEL_CODE = NEW.VD_VESSEL_CODE and VD_SHIPPING_LINE_CODE = VD_SHIPPING_LINE_CODE and 
                VD_SHIPPING_AGENT_CODE = NEW.VD_SHIPPING_AGENT_CODE and VD_MASTER_NAME = NEW.VD_MASTER_NAME and VD_PORT_OF_ARRIVAL = NEW.VD_PORT_OF_ARRIVAL and 
                VD_LAST_PORT_CALLED = NEW.VD_LAST_PORT_CALLED and VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL1 = NEW.VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL1 and
                VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL2 = NEW.VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL2 and VD_VESSEL_TYPE = NEW.VD_VESSEL_TYPE and VD_TOTAL_NO_OF_LINES = NEW.VD_TOTAL_NO_OF_LINES
        and VD_BRIEF_CARGO_DESCRIPTION = NEW.VD_BRIEF_CARGO_DESCRIPTION and VD_EXPECTED_ARRIVAL_DATETIME = NEW.VD_EXPECTED_ARRIVAL_DATETIME and VD_LIGHT_HOUSE_DUES = NEW.VD_LIGHT_HOUSE_DUES
        and VD_SAME_BOTTOM_CARGO = NEW.VD_SAME_BOTTOM_CARGO and VD_SHIP_STORES_DECLARATION = NEW.VD_SHIP_STORES_DECLARATION and VD_CREW_LIST_DECLARATION = NEW.VD_CREW_LIST_DECLARATION and VD_PASSENGER_LIST = NEW.VD_PASSENGER_LIST
        and VD_CREW_EFFECT_DECLARATION = NEW.VD_CREW_EFFECT_DECLARATION and VD_MARITIME_DECLARATION = NEW.VD_MARITIME_DECLARATION and VD_TERMINAL_OPERATOR_CODE = NEW.VD_TERMINAL_OPERATOR_CODE)) THEN


    INSERT INTO fifo_test.igm_vessel_details (VD_MESSAGE_TYPE, VD_CUSTOM_HOUSE_CODE, VD_IGM_NO, VD_IGM_DATE, VD_IMO_CODE_OF_VESSEL,
    VD_VESSEL_CODE,VD_SHIPPING_LINE_CODE,VD_SHIPPING_AGENT_CODE,VD_MASTER_NAME,VD_PORT_OF_ARRIVAL,
    VD_LAST_PORT_CALLED,VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL1,VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL2,VD_VESSEL_TYPE,
    VD_TOTAL_NO_OF_LINES,VD_BRIEF_CARGO_DESCRIPTION,VD_EXPECTED_ARRIVAL_DATETIME) 
    VALUES (NEW.VD_MESSAGE_TYPE, NEW.VD_CUSTOM_HOUSE_CODE, NEW.VD_IGM_NO, NEW.VD_IGM_DATE, NEW.VD_IMO_CODE_OF_VESSEL,
    NEW.VD_VESSEL_CODE,NEW.VD_SHIPPING_LINE_CODE,NEW.VD_SHIPPING_AGENT_CODE,NEW.VD_MASTER_NAME,NEW.VD_PORT_OF_ARRIVAL,
    NEW.VD_LAST_PORT_CALLED,NEW.VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL1,NEW.VD_PORT_CALLED_BEFORE_PORT_OF_ARRIVAL2,NEW.VD_VESSEL_TYPE,
    NEW.VD_TOTAL_NO_OF_LINES,NEW.VD_BRIEF_CARGO_DESCRIPTION,NEW.VD_EXPECTED_ARRIVAL_DATETIME);


  END IF;

END$$
DELIMITER ;

上面的脚本不起作用。

Answer 1

与mysql文档无关，触发器必须失败，然后插入操作将被取消。1尝试创建一个signal sqlstate