mysql update 加if_MySQL IF语句在UPDATE语句的触发器内

它似乎是UPDATE触发器 . 不是吗？如果是这样，请尝试此代码(有一些语法错误) -

UPDATE

tbl_product_types

SET

UpdatedTS = NOW()

WHERE ID = NEW.ProductTypeID;

IF (SELECT length(Old.ProductRef) > 0) THEN

-- We have already stored the product reference so run a replace

UPDATE

tbl_product_type_search AS STable

SET

`STable.Search` = REPLACE(`Search`, CONCAT(Old.ProductRef, ' '), NEW.ProductRef)

WHERE

`STable.ProductTypeID` = OLD.ProductTypeID;

ELSE

-- We haven't yet stored the product reference, store it

UPDATE

tbl_product_type_search AS STable

SET

`STable.Search` = CONCAT(NEW.ProductRef, ' ', `STable.Search`)

WHERE

STable.ProductTypeID = NEW.ProductTypeID;

END IF;

Two UPDATE statements into one:

UPDATE

tbl_product_type_search AS STable

SET

`STable.Search` =

IF(

LENGTH(OLD.ProductRef) > 0,

REPLACE(`Search`, CONCAT(OLD.ProductRef,' '), NEW.ProductRef),

CONCAT(NEW.ProductRef, ' ', `STable.Search`)

)

WHERE

LENGTH(OLD.ProductRef) > 0 AND `STable.ProductTypeID` = OLD.ProductTypeID

OR

LENGTH(OLD.ProductRef) <= 0 AND STable.ProductTypeID = NEW.ProductTypeID

About delimiters:

DELIMITER $$

CREATE TRIGGER trigger_name

AFTER UPDATE

ON table_name

FOR EACH ROW

BEGIN

UPDATE tbl_product_types...;

other statements...;

END

$$

DELIMITER ;