CODEWITHSUNDEEP
mysqldatetimetriggers
Matthew G
Matthew G

Reputation: 1090

Compare dates in MySQL trigger

I have two tables, round and event. One round has many events.

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),
);

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.

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.

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 < curSdate) then
        update round set round_start_date = NEW.event_date where round_id = NEW.round_id;
    else if (NEW.event_date > curEdate) then
        update round set round_end_date = NEW.event_date where round_id = NEW.round_id;
    end if;
end;

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"

EDIT 2: Updated with a delimiter set

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 < curSdate) then
        update round set round_start_date = NEW.event_date where round_id = NEW.round_id;
    else if (NEW.event_date > curEdate) then
        update round set round_end_date = NEW.event_date where round_id = NEW.round_id;
    end if;
end$$

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"

Upvotes: 1

Views: 5858

Answers (2)

lc.
lc.

Reputation: 116528

MySQL is probably stopping at the first ';', interpreting your command as:

create trigger update_round_date 
after insert on event for each row
begin
    declare curSdate datetime;

You have to set your delimiter to something else first, then terminate the create trigger command with that delimiter instead (and put the delimiter back at the end):

delimiter ^

create trigger update_round_date 
after insert on event for each row
begin
    ...
end;

^

delimiter ;

I believe the last semicolon after end may be necessary.

Upvotes: 1

Devart
Devart

Reputation: 122002

There may be a problem with delimiters in phpmyadmin, try to use this trigger -

CREATE TRIGGER trigger1
AFTER INSERT
ON event
FOR EACH ROW
  UPDATE
    round
  SET
    round_start_date =
      IF(NEW.event_date < round_start_date, NEW.event_date, round_start_date),
    round_end_date =
      IF(NEW.event_date > round_end_date, NEW.event_date, round_end_date)
  WHERE
    round_id = NEW.round_id;

Upvotes: 1

Related Questions