CODEWITHSUNDEEP
mysqldatabase-trigger
Noor Chaudhry
Noor Chaudhry

Reputation: 73

SQL creating a trigger to insert and update table

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;

Upvotes: 0

Views: 40

Answers (1)

Matt Raines
Matt Raines

Reputation: 4218

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.

Upvotes: 1

Related Questions