Reputation: 1167
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
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