Multiple commands in a Trigger (Update multiple columns with different conditions)

Question

I have an issue which i'm hoping someone out there will be able to assist.

I'm trying to write a trigger which takes the datetime stamp and puts just the date into a new column. But likewise, also if a value in a different column is equal to x, then replace it with y.

I can get individual statements to work (i.e if I have just the date code or the replacement code individually it works fine), but I can't get them to both work in the same trigger.

SET NEW.date = LEFT(NEW.entrydate, 10);
IF 
    (NEW.connect_ip = "1.2.3.4") 
    THEN SET 
    NEW.connect_ip = "0.0.0.0";
    END IF

Answer 1

Multi-statement triggers need BEGIN and END around their "body", and usually DELIMITER overridden around their declaration.

Example/Template:

DELIMITER $$

CREATE TRIGGER [blah blah]
BEGIN
   [do stuff]
END$$

DELIMITER ;

Alternatively, for your specific case, I noticed an answer the other day with a possiblity I had overlooked; you can set multiple things in a SET statement:

SET NEW.date = LEFT(NEW.entrydate, 10)
    , NEW.connect_ip = IF(NEW.connect_ip = "1.2.3.4", "0.0.0.0", "1.2.3.4")
    ;