1

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...

0

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;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.