Updating a date as part of a trigger?

Question

I'm trying to keep a list of current content and archived content in my PHP Internet application. I want to be able to identify the content that as archived as having an enddate, and the content that is not archived as having no enddate. It would be ideal if I could create more paths, but I'm hoping to just start here.

The first thing I'm noticing is that I'm getting a syntax error at the " on line 1, but there is no double quote where I create the table. The second issue I'm having is the use of TIMESTAMP as a datatype. I tried to use CURRENT_TIMESTAMP for the startdate, and it returned syntax errors. The final problem I am having is with the trigger construction. I'm not able to get down far enough to troubleshoot it. As soon as I get past the references, I'll also try to troubleshoot that.

CREATE TABLE plan(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  startdate TIMESTAMP NOT NULL,
  enddate TIMESTAMP);
CREATE TABLE level(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  description VARCHAR(500),
  startdate TIMESTAMP NOT NULL,
  enddate TIMESTAMP);
CREATE TABLE planIDxlevelID(
  planID INT NOT NULL REFERENCES plan(id),
  levelID INT NOT NULL REFERENCES level(id),
  arXORcur ENUM('archive', 'current');
);

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON plan
  FOR EACH ROW BEGIN
    INSERT INTO plan(id, plan, startdate, enddate)
      SET id = LAST_INSERT_ID( id + 1), name = NEW.name, startdate = NEW.UTC_TIMESTAMP, enddate = NULL;
    UPDATE plan(enddate) WHERE plan.id = OLD.id
      SET enddate = UTC_TIMESTAMP;
    INSERT INTO planIDxlevelID(planID, levelID, arXORcur)
      SET planID = NEW.planID, levelID = OLD.levelID, arXORcur = current;
    UPDATE planIDxlevelID(planID, levelID, arXORcur)  WHERE planID = OLD.planID
      SET planID = OLD.planID, levelID = OLD.levelID, arXORcur = archive;
  END;
|

delimiter ;

INSERT INTO plan (name) VALUES
  "Frogs", "Toys", "Trucks", "Nature", "Seasons",
  "Construction", "Candy", "Rainbows", "Telephone", "Breakfasts";

Answer 1

TIMESTAMP vs DATETIME

This is a classic gotcha: TIMESTAMP is a special datatype that is updated with "now" every time the row is touched, whether you update it to another value or leave it alone. What you want is DATETIME.

Next, you have numerous syntax and other errors. The following executes without error, however it may not now have the logic you want, but you can edit it to fix that:

CREATE TABLE plan (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  startdate DATETIME NOT NULL, -- changed TIMESTAMP to DATETIME
  enddate DATETIME -- changed TIMESTAMP to DATETIME
);

CREATE TABLE level (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(32),
  description VARCHAR(500),
  startdate DATETIME NOT NULL, -- changed TIMESTAMP to DATETIME
  enddate DATETIME -- changed TIMESTAMP to DATETIME
);

CREATE TABLE planIDxlevelID (
  planID INT NOT NULL REFERENCES plan(id),
  levelID INT NOT NULL REFERENCES level(id),
  arXORcur ENUM('archive', 'current')
);

DROP TRIGGER IF EXISTS test_insert;
delimiter |
CREATE TRIGGER test_insert BEFORE INSERT ON plan
  FOR EACH ROW BEGIN
    INSERT INTO plan (id, plan, startdate)
    values (LAST_INSERT_ID() + 1, NEW.name, NEW.UTC_TIMESTAMP());

    INSERT INTO planIDxlevelID (planID, levelID, arXORcur)
    values (NEW.ID, null, arXORcur = current);    
  END;|
delimiter ;

DROP TRIGGER IF EXISTS test_update;
delimiter |
CREATE TRIGGER test_update BEFORE UPDATE ON plan
  FOR EACH ROW BEGIN
    UPDATE plan SET
    enddate = UTC_TIMESTAMP
    WHERE plan.id = OLD.id;

    UPDATE planIDxlevelID SET
    planID = NEW.ID,
    levelID = null,
    arXORcur = archive
    WHERE planID = OLD.ID;
  END;|
delimiter ;

INSERT INTO plan (name) VALUES
  ("Frogs"), ("Toys"), ("Trucks"), ("Nature"), ("Seasons"),
  ("Construction"), ("Candy"), ("Rainbows"), ("Telephone"), ("Breakfasts");