BkBUpdt8

1Updating a date as part of a trigger?
2CREATE TABLE plan(
3  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
4  name VARCHAR(32),
5  startdate TIMESTAMP NOT NULL,
6  enddate TIMESTAMP);
7CREATE TABLE level(
8  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
9  name VARCHAR(32),
10  description VARCHAR(500),
11  startdate TIMESTAMP NOT NULL,
12  enddate TIMESTAMP);
13CREATE TABLE planIDxlevelID(
14  planID INT NOT NULL REFERENCES plan(id),
15  levelID INT NOT NULL REFERENCES level(id),
16  arXORcur ENUM('archive', 'current');
17);
18
19delimiter |
20
21CREATE TRIGGER testref BEFORE INSERT ON plan
22  FOR EACH ROW BEGIN
23    INSERT INTO plan(id, plan, startdate, enddate)
24      SET id = LAST_INSERT_ID( id + 1), name = NEW.name, startdate = NEW.UTC_TIMESTAMP, enddate = NULL;
25    UPDATE plan(enddate) WHERE plan.id = OLD.id
26      SET enddate = UTC_TIMESTAMP;
27    INSERT INTO planIDxlevelID(planID, levelID, arXORcur)
28      SET planID = NEW.planID, levelID = OLD.levelID, arXORcur = current;
29    UPDATE planIDxlevelID(planID, levelID, arXORcur)  WHERE planID = OLD.planID
30      SET planID = OLD.planID, levelID = OLD.levelID, arXORcur = archive;
31  END;
32|
33
34delimiter ;
35
36INSERT INTO plan (name) VALUES
37  "Frogs", "Toys", "Trucks", "Nature", "Seasons",
38  "Construction", "Candy", "Rainbows", "Telephone", "Breakfasts";
39	
40CREATE TABLE plan (
41  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
42  name VARCHAR(32),
43  startdate DATETIME NOT NULL, -- changed TIMESTAMP to DATETIME
44  enddate DATETIME -- changed TIMESTAMP to DATETIME
45);
46
47CREATE TABLE level (
48  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
49  name VARCHAR(32),
50  description VARCHAR(500),
51  startdate DATETIME NOT NULL, -- changed TIMESTAMP to DATETIME
52  enddate DATETIME -- changed TIMESTAMP to DATETIME
53);
54
55CREATE TABLE planIDxlevelID (
56  planID INT NOT NULL REFERENCES plan(id),
57  levelID INT NOT NULL REFERENCES level(id),
58  arXORcur ENUM('archive', 'current')
59);
60
61DROP TRIGGER IF EXISTS test_insert;
62delimiter |
63CREATE TRIGGER test_insert BEFORE INSERT ON plan
64  FOR EACH ROW BEGIN
65    INSERT INTO plan (id, plan, startdate)
66    values (LAST_INSERT_ID() + 1, NEW.name, NEW.UTC_TIMESTAMP());
67
68    INSERT INTO planIDxlevelID (planID, levelID, arXORcur)
69    values (NEW.ID, null, arXORcur = current);    
70  END;|
71delimiter ;
72
73DROP TRIGGER IF EXISTS test_update;
74delimiter |
75CREATE TRIGGER test_update BEFORE UPDATE ON plan
76  FOR EACH ROW BEGIN
77    UPDATE plan SET
78    enddate = UTC_TIMESTAMP
79    WHERE plan.id = OLD.id;
80
81    UPDATE planIDxlevelID SET
82    planID = NEW.ID,
83    levelID = null,
84    arXORcur = archive
85    WHERE planID = OLD.ID;
86  END;|
87delimiter ;
88
89INSERT INTO plan (name) VALUES
90  ("Frogs"), ("Toys"), ("Trucks"), ("Nature"), ("Seasons"),
91  ("Construction"), ("Candy"), ("Rainbows"), ("Telephone"), ("Breakfasts");