Comments - Trigger Overview

3 years, 8 months ago Jan Steinman

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):

INSERT INTO general_journal SET
  sales_record = NEW.ID;
  general_journal = last_insert_id,
  posted = 'Y',

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?

3 years, 6 months ago Jan Steinman

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:

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;

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

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.