SQL creating a trigger to insert and update table

Question

I have three tables a test_cases table, a schedule table, an aggregate_summary table

test_cases

 caseID | file_name
--------+-------------------------
      1 | Test 1
      2 | Test 2
      3 | Test 3

schedule

| scheduleID | caseID | schedule_time
+------------+--------+---------------------
|          1 |      1 | 2016-02-29 02:15:00
|          2 |      2 | 2016-02-29 12:00:00
|          3 |      3 | 2016-02-27 12:00:00
|          4 |      2 | 2016-02-25 07:26:00
|          5 |      1 | 2016-02-26 07:37:00
|          6 |      2 | 2016-02-27 07:39:00
|          7 |      2 | 2016-02-28 07:25:00
|          8 |      1 | 2016-02-29 08:38:00
|          9 |      2 | 2016-02-29 07:08:00

aggregate_summary which has start_time and file name and other fields

I want to create a trigger so after a test is scheduled it puts the schedule_time into start_time (on aggregate_summary table) and puts file_name(from test_cases) into file_name field on aggregate_summary table.

I cannot get the syntax right:

CREATE TRIGGER OnScheduleInsert AFTER INSERT ON schedule FOR EACH ROW BEGIN INSERT INTO aggregate_summary (start_time) VALUES(NEW.schedule_time)
UPDATE aggregate_summary SET file_name=(SELECT file_name FROM test_cases WHERE NEW.caseID=test_cases.caseID) END;

Matt Raines · Accepted Answer · 2016-03-01 21:35:59Z

1

To execute queries in the body of the trigger they must be separated by semicolons. In order to have your MySQL client not interpret the semicolon as the end of the CREATE TRIGGER statement, you might first need to change the delimiter to something other than a semicolon, then change it back afterwards.

DELIMITER //
CREATE TRIGGER OnScheduleInsert
AFTER INSERT ON schedule
FOR EACH ROW
BEGIN
    INSERT INTO aggregate_summary (start_time) VALUES(NEW.schedule_time);
    UPDATE aggregate_summary SET file_name=(SELECT file_name FROM test_cases WHERE NEW.caseID=test_cases.caseID);
END//
DELIMITER ;

I don't think the body of the trigger quite does what you want it to but I'm afraid I'm not really clear what the desired behaviour of the trigger is. Still, at least it's now syntactically valid.

answered Mar 1, 2016 at 21:35

Matt Raines

4,2478 gold badges34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL creating a trigger to insert and update table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related