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

migrated from programmers.stackexchange.com Jun 12 '15 at 3:36

This question came from our site for professionals, academics, and students working within the systems development life cycle.

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;

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.