CODEWITHSUNDEEP
mysqltriggers
Riples
Riples

Reputation: 1167

Can't Setup MySQL Trigger To INSERT Into Second Table

I have two tables which I use to store call details in. One table (Call_Detail) stores the header details against each call that gets entered, the second (Call_History) stores every comment against the call. So a single call will only appear ONCE in the Call_Detail table, but may appear multiple times in the Call_History table.

I currently run a Query to return the latest comment against a group of calls. So, I return the header details out of Call_Detail and then cross reference against the Call_History to find the 'newest' comment (thanks to some outside help). However, this Query can be quite time consuming when running against a large number of calls.

Therefore, I'm thinking to optimize my Query, I want to setup a trigger that records these details. I am wanting to catch any INSERT command into the Call_History table and record the comment and date/time into the Call_Detail table against the relevant call ID.

So far I have the following but it doesn't like my syntax for some reason:

DELIMITER $$
CREATE TRIGGER Last_Call_Update
AFTER INSERT ON call_history
FOR EACH ROW
BEGIN
UPDATE call_detail
SET last_updated = NEW.updated_at, last_commment = NEW.body
WHERE id = NEW.ticket_id
END $$
DELIMITER ;

Upvotes: 0

Views: 156

Answers (1)

Naktibalda
Naktibalda

Reputation: 14120

Add semicolon after UPDATE statement

DELIMITER $$
CREATE TRIGGER Last_Call_Update
AFTER INSERT ON call_history
FOR EACH ROW
BEGIN
    UPDATE call_detail
    SET last_updated = NEW.updated_at, last_commment = NEW.body
    WHERE id = NEW.ticket_id;
END $$
DELIMITER ;

Upvotes: 1

Related Questions