Search code examples
mysqlsqlfiddle

Cannot put INSERT STATEMENT inside TRIGGER body on SQL Fiddle


I have a simple trigger, it runs on SQL Fiddle but it doesn't allow me to move my INSERT STATEMENT inside the trigger's body. my code on sqlFiddle I simply want to move this line

INSERT INTO t2(start_date) VALUES (CURDATE());

inside the body of this trigger

CREATE TRIGGER trig1
AFTER UPDATE ON t1
FOR EACH ROW 
BEGIN
  -- here, i want to put the simple INSERT STATEMENT here
END;

can't seem to get it to accept that on sqlFiddle
When I try it

CREATE TRIGGER trig1
AFTER UPDATE ON t1
FOR EACH ROW 
BEGIN
  INSERT INTO t2(start_date) VALUES (CURDATE());
END;

it gives me this error:Schema Creation Failed: 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 4:


Solution

  • Change the delimiter to / (slash).

    CREATE TABLE t1
        (
         id INT auto_increment primary key, 
         value INT(11) 
        )
    /
    CREATE TABLE t2
        (
         id INT auto_increment primary key,
         start_date DATE
        )
    /
    
    
    INSERT INTO t1( value ) VALUES( 100 )
    /
    
    CREATE TRIGGER trig1
    AFTER UPDATE ON t1
    FOR EACH ROW 
    BEGIN
       INSERT INTO t2( start_date ) VALUES ( now() );
    END;
    /
    
    
    UPDATE t1 SET value = 10
    /
    

    Demo --> http://sqlfiddle.com/#!2/aecc7/1