Question

How do I change this following SQL trigger below so that it does the following:
If the ThingPrice is not specified, ThingPrice = ThingCost * 7

BEGIN

    IF NEW.ThingPrice = NEW.ThingCost THEN   /* this line is incorrect, and needs to be fixed */
        SET NEW.ThingPrice = NEW.ThingPrice * 7;
    END IF;
    
END

Answer 1

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Fixed:-

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

I think this should do the trick...

BEGIN

    IF (NEW.ThingPrice <=> NEW.ThingCost) THEN   /* FIXED */
        SET NEW.ThingPrice = NEW.ThingPrice * 7;
    END IF;
    
END

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Explanation :-

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

BEGIN

    IF NEW.ThingPrice *(( = ))* NEW.ThingCost THEN   /* this line is incorrect, and needs to be fixed */
        SET NEW.ThingPrice = NEW.ThingPrice * 7;
    END IF;
    
END

# THE PROBLEM ABOVE MIGHT BE BECAUSE OF USING " = " INSTEAD OF "<=>" (NULL SAFE EQUAL).

Whenever there is a possibility that you might have to deal with "NULL" values you should use "NULL SAFE EQUALS".

(Note - On the basis of present amount of information i think this might be the solution. But as only a little information is present, there is chance it might not work and problem lies somewhere else.)