How can I use a trigger to update a field in one table, based on data from record insert in another?

I'm new to working with triggers and am having a hard time understanding how to write a trigger to update a field in one table, when a record is inserted in another.

To elaborate, I have 2 tables: servTickets and servTicketNotes.

servTickets has several text fields for customer, contact, phone, email, problem description, status, etc...the PK in this table is an INT field called callID.

servTicketNotes has only 2 fields - again, the PK is an INT field 'callID' and there is a BLOB field called image which stores an image of a service report.

What I'm struggling to do is have a trigger update the status field in servTickets with a value of Closed when a new record is inserted into servTicketNotes.

I'm confused if this is an INSERT AFTER or BEFORE or BOTH scenario, but basically if a report is sent in (thereby creating a record in servTicketNotes, I want the trigger to seek out the record with the same callID in the servTickets table and change the value of status to 'Closed'.

This seems like it should be so simple, but I can't seem to grasp how to get started...

Thanks in advance for your help/guidance!

Answers


is it probably a POST trigger - which means:

AFTER you have committed the incoming record, you want to take further action - i.e. inserting into the other table.

if you do it PRE commit, then you would worry about some error happening on the Notes and you might end up with an incorrect update to the status.


You can do this with an AFTER INSERT trigger. Try something like this:

DELIMITER $$

DROP TRIGGER IF EXISTS tr_a_ins_servTicketNotes $$

CREATE TRIGGER pabeta.tr_a_ins_servTicketNotes AFTER INSERT ON servTicketNotes FOR EACH ROW BEGIN
  update servTickets
  set status = 'Closed'
  where callID = NEW.callID;

END $$

DELIMITER ;

Need Your Help

Can't start MySQL5.5 on Ubuntu 12.04 - "dpkg: dependency problems"

ubuntu-12.04 mysql-5.5

This appears to be a common problem on 12.04. I've tried everthing in this thread including:

How to customize a toolbar in Eclipse?

java eclipse customization

I am trying to customize Eclipse to speed up development and minimize distractions for Android Java development.