title: Compare dates in MySQL trigger
body: <p>I have two tables, round and event.  One round has many events.</p>
  
  <pre><code>create table round (
      round_id INTEGER AUTO_INCREMENT NOT NULL,
      round_start_date DATETIME NOT NULL,
      round_end_date DATETIME NOT NULL,
      CONSTRAINT round_pk PRIMARY KEY (round_id)
  );
  
  create table event (
      event_id INTEGER AUTO_INCREMENT NOT NULL,
      round_id INTEGER NOT NULL,
      event_date DATETIME NOT NULL,
      CONSTRAINT event_pk PRIMARY KEY (event_id),
      CONSTRAINT round_fk FOREIGN KEY (round_id) REFERENCES round (round_id),
  );
  </code></pre>
  
  <p>When a row is inserted into the event table, I want to use a trigger to compare the event_date field of the newly inserted row to the round_start_date and round_end_date fields in its corresponding entry in the round table.  If event_date is earlier than round_start_date, round_start_date should be updated with the new event_date.  If event_date is after round_end_date, round_end_date should be updated with the new event_date.</p>
  
  <p>This is my trigger.  It does not work, and I do not understand why.  I cannot find anywhere on the web where anyone else has tried to use a datetime type in a trigger, so I have no frame of reference for where I am going wrong.</p>
  
  <pre><code>create trigger update_round_date
  after insert on event for each row
  begin
      declare curSdate datetime;
      declare curEdate datetime;
      set curSdate = (select round_start_date from round where round_id = NEW.round_id);
      set curEdate = (select round_end_date from round where round_id = NEW.round_id);
      if (NEW.event_date &lt; curSdate) then
          update round set round_start_date = NEW.event_date where round_id = NEW.round_id;
      else if (NEW.event_date &gt; curEdate) then
          update round set round_end_date = NEW.event_date where round_id = NEW.round_id;
      end if;
  end;
  </code></pre>
  
  <p>EDIT: I simply can't create the trigger. phpMyAdmin gives me this 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 4"</p>
  
  <p>EDIT 2: Updated with a delimiter set</p>
  
  <pre><code>DELIMITER $$
  create trigger update_round_date
  after insert on event for each row
  begin
      declare curSdate datetime;
      declare curEdate datetime;
      set curSdate = (select round_start_date from round where round_id = NEW.round_id);
      set curEdate = (select round_end_date from round where round_id = NEW.round_id);
      if (NEW.event_date &lt; curSdate) then
          update round set round_start_date = NEW.event_date where round_id = NEW.round_id;
      else if (NEW.event_date &gt; curEdate) then
          update round set round_end_date = NEW.event_date where round_id = NEW.round_id;
      end if;
  end$$
  </code></pre>
  
  <p>This returns the 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 13"</p>