Comments - Trigger Overview

Maybe good to know:

To check on an update trigger whether a column needs to be updated you can do old.value<>new.value as an if statement:

if old.value<>new.value then
 set new.value=whatever you need;
end if;

This even works on encrypted fields so you don't need to decrypt or encrypt values before comparing. It seems this is to be done by database itself "automatically".

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.

What does text "Statements that affect multiple rows will fail before inserting the current row" (in the "Triggers and errors" section) means?

That means that, if inserting multiple rows with a BEFORE trigger, an error in the trigger will cause ALL of the insertions to fail.