MySQL Trigger usage : if column is null set other column null

mysql, sql

Solution

This is syntax for trigger:

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
     FOR EACH ROW
     BEGIN
         IF NEW.amount < 0 THEN
             SET NEW.amount = 0;
         ELSEIF NEW.amount > 100 THEN
            SET NEW.amount = 100;
         END IF;
    END;//
 delimiter ;

...and your code is here:

DELIMITER //
CREATE TRIGGER check_null_x2 BEFORE INSERT ON variations
    FOR EACH ROW
    BEGIN
        IF NEW.x2 IS NULL THEN 
            SET NEW.x2_option1 = NULL;
            SET NEW.x2_option2 = NULL;
        END IF;
    END$$ -- THIS LINE SHOULD BE: "END;//"
DELIMITER ;

EDIT: The official Documentation says the following:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command. The following example shows how to do this for the dorepeat() procedure just shown. The delimiter is changed to // to enable the entire definition to be passed to the server as a single statement, and then restored to ; before invoking the procedure. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself.

Problem

How do I realize the following in MySQL with Triggers: When value of some column is null -> set other column values to null and when value of some column is not null -> set other column values to null table definition: ``` CREATE TABLE variations ( id int(10) NOT NULL, x1 int(10) NOT NULL, x2 int(10), x1_option1 BOOL, x2_option1 BOOL, x1_option2 varchar(10), x2_option2 varchar(10) ); ``` The idea is that we have 2 Elements, `x1`and `x2`. While `x1`is mandatory, `x2`is optional and can be null. Both, x1 and x2 have two options: `x1_option1` , `x2_option1`, `x1_option2` and `x2_option2`. The first rule should be that when x2 is null, both options for x2 (`x2_option1`, `x2_option2`) must also be null. My attempt: ``` CREATE TRIGGER check_null_x2 BEFORE INSERT ON variations FOR EACH ROW BEGIN IF NEW.x2 IS NULL THEN SET NEW.x2_option1 = NULL; SET NEW.x2_option2 = NULL; END IF; END$$ ``` Throws Error: ``` #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6 ``` Can you please help me figuring out whats wrong? I just dont understand what '' means. The second rule should be that there can only be one of the two options selected. that means if `x2_option1` is NOT NULL, `x2_options2` must be NULL. In general i think this can be done the same way as the first rule. My question: how can i do multiple 'IF', 'ELSE IF' etc in one trigger?

Original source