Comments - Trigger Overview

How can one use a trigger to install a back-pointer into the triggering record?

For example, entering a sales record causes a general journal record to be created with a pointer ("NEW.ID") to the sales record, and then a reference to the created record in the sales record?

In the trigger (simplified):

BEGIN
INSERT INTO general_journal SET
  sales_record = NEW.ID;
UPDATE sales SET
  general_journal = last_insert_id,
  posted = 'Y',
WHERE ID = NEW.ID;
END

When I attempt such a thing, it tells me: "Can't update table 'sales' in stored function/trigger because it is already used by statement which invoked this stored function/trigger"

Surely, using a trigger to create a record in some other table, and then to save a reference to that new record should be a common thing to do, no?

Okay, answering my own question: I should have known this, but the answer is to use a seperate "join table" that contains a pointer to both the originating record and the inserted record:

BEGIN
INSERT INTO general_journal SET
  -- other data as needed;
INSERT INTO `sales-general_journal` SET
  general_journal_ID = last_insert_id,
  sales_ID = NEW.ID;
END

Now, both records are available from either record by doing a LEFT JOIN on the `sales-general_journal` table.