MySQL trigger level

Question

I'm looking for a way to find out if a row in a table is updated by a trigger or by regular input.

In example, I have two tables:

TABLE 1

table1_id INT(11)
name VARCHAR(255)
last_date DATETIME

TABLE 2

table2_id INT(11)
table1_id INT(11)

When an user updates a row in Table 1, I'm updating last_date to NOW(). However when an user inserts a row in Table 2 and I want to change i.e. the name field in Table 1 it will also update the last_date. How can I prevent this?

In MSSQL there is TRIGGER_NESTLEVEL(), only I'm not quite sure MySQL triggers are really nested...

Answer 1

Fixed this by setting a session variable in the trigger of Table2 and check if this is NULL in the trigger of Table1.

Trigger Table1:

IF @TABLE2_TRIGGER IS NULL THEN
    SET NEW.last_date = NOW();
END IF;

Trigger Table2:

SET @TABLE2_TRIGGER = 1;
UPDATE table1 SET name = 'example' WHERE table1_id = NEW.table1_id;
SET @TABLE2_TRIGGER = NULL;