MariaDBでSET列を含むトリガーが動作しない問題を解決する2つの方法

2024-04-16

MariaDBにおけるSET列のトリガー動作に関する問題と解決策

MariaDBにおいて、SET列を含むテーブルに対してトリガーを設定する場合、予期しない動作が発生することがあります。具体的には、トリガー内でSET列の値を更新しようとすると、以下のエラーが発生する可能性があります。

Error: Cannot update a table that is already being used by the statement that invoked the trigger

このエラーは、トリガーが実行される際に、SET列を含む同じテーブルが既に読み書きされていることが原因です。MariaDBは、トリガー内で同じテーブルを更新することを許可していないため、このエラーが発生します。

解決策

この問題を解決するには、以下の2つの方法があります。

ストアドプロシージャは、複数のSQLステートメントをグループ化して実行できるプログラムです。トリガー内でSET列の値を更新する代わりに、ストアドプロシージャを作成し、トリガーからそのストアドプロシージャを呼び出すことができます。ストアドプロシージャは、トリガーとは別のトランザクションで実行されるため、SET列を含むテーブルを更新してもエラーが発生しません。

例

-- ストアドプロシージャを作成する
CREATE PROCEDURE update_set_column(table_name VARCHAR(255), column_name VARCHAR(255), new_value INT)
BEGIN
    UPDATE table_name
    SET column_name = new_value;
END PROCEDURE;

-- トリガーを作成する
CREATE TRIGGER update_set_column_after_update
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    CALL update_set_column('your_table', 'your_set_column', NEW.your_set_column);
END TRIGGER;

別のテーブルを使用する

SET列の値を更新するために、別のテーブルを使用することができます。トリガー内で、SET列の値を別のテーブルに挿入し、その値を使用して元のテーブルを更新します。

-- 別のテーブルを作成する
CREATE TABLE set_column_values (
    table_name VARCHAR(255),
    column_name VARCHAR(255),
    new_value INT
);

-- トリガーを作成する
CREATE TRIGGER update_set_column_after_update
AFTER UPDATE ON your_table
FOR EACH ROW
BEGIN
    INSERT INTO set_column_values (table_name, column_name, NEW.your_set_column)
    VALUES ('your_table', 'your_set_column', NEW.your_set_column);
END TRIGGER;

-- 別のトリガーを作成して、`set_column_values` テーブルに基づいて元のテーブルを更新する
CREATE TRIGGER update_your_table_from_set_column_values
AFTER INSERT ON set_column_values
FOR EACH ROW
BEGIN
    UPDATE your_table
    SET your_set_column = NEW.new_value
    WHERE your_table.id = NEW.table_id;

    DELETE FROM set_column_values
    WHERE table_id = NEW.table_id;
END TRIGGER;

注意事項

上記の解決策を使用する場合は、以下の点に注意する必要があります。

ストアドプロシージャを使用する場合は、ストアドプロシージャが原子的に実行されるようにする必要があります。
別のテーブルを使用する場合は、2つのトリガーが競合しないようにする必要があります。

MariaDBにおけるSET列のトリガー動作に関する問題のサンプルコード

以下のテーブル users に対して、active 列が更新された後に、last_updated 列を自動的に更新するトリガーを作成します。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    active TINYINT(1) NOT NULL DEFAULT 1,
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

しかし、このトリガーを設定しても、last_updated 列は更新されません。

以下の2つの方法で問題を解決できます。

ストアドプロシージャを使用する

-- ストアドプロシージャを作成する
CREATE PROCEDURE update_last_updated(user_id INT, new_active TINYINT(1))
BEGIN
    UPDATE users
    SET last_updated = CURRENT_TIMESTAMP
    WHERE id = user_id
    AND active = new_active;
END PROCEDURE;

-- トリガーを作成する
CREATE TRIGGER update_last_updated_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    CALL update_last_updated(NEW.id, NEW.active);
END TRIGGER;

-- 別のテーブルを作成する
CREATE TABLE last_updated_values (
    user_id INT,
    new_last_updated TIMESTAMP
);

-- トリガーを作成する
CREATE TRIGGER update_last_updated_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    INSERT INTO last_updated_values (user_id, new_last_updated)
    VALUES (NEW.id, CURRENT_TIMESTAMP);
END TRIGGER;

-- 別のトリガーを作成して、`last_updated_values` テーブルに基づいて元のテーブルを更新する
CREATE TRIGGER update_users_from_last_updated_values
AFTER INSERT ON last_updated_values
FOR EACH ROW
BEGIN
    UPDATE users
    SET last_updated = NEW.new_last_updated
    WHERE id = NEW.user_id;

    DELETE FROM last_updated_values
    WHERE user_id = NEW.user_id;
END TRIGGER;

説明

この方法では、トリガー内でストアドプロシージャを呼び出して、last_updated 列を更新します。ストアドプロシージャは、トリガーとは別のトランザクションで実行されるため、SET列を含むテーブルを更新してもエラーが発生しません。

この方法では、last_updated 列の値を更新するために別のテーブルを使用します。トリガー内で、last_updated 列の値を別のテーブルに挿入し、その値を使用して元のテーブルを更新します。

ストアドプロシージャを使用する場合は、コードがより簡潔になります。
別のテーブルを使用する場合は、コードがより冗長になりますが、ストアドプロシージャを使用するよりもパフォーマンスが向上する可能性があります。

MariaDBにおけるSET列のトリガー動作に関する問題のその他の解決策

BEFORE UPDATEトリガーを使用すると、active列が更新される前にlast_updated列を更新することができます。

CREATE TRIGGER update_last_updated_before_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users
    SET last_updated = CURRENT_TIMESTAMP
    WHERE id = NEW.id
    AND active = OLD.active;
END TRIGGER;

CREATE TRIGGER update_last_updated_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    UPDATE users
    SET last_updated = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END TRIGGER;

トリガー内でNEWとOLDを使用せずに、UPDATEステートメントを使用してlast_updated列を更新することができます。

CREATE TRIGGER update_last_updated_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    UPDATE users
    SET last_updated = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END TRIGGER;

トリガーを使用せずに、定期的にlast_updated列を更新するバッチジョブを実行することもできます。

CREATE TABLE last_updated_timestamps (
    user_id INT,
    last_updated TIMESTAMP
);

INSERT INTO last_updated_timestamps (user_id, last_updated)
SELECT id, last_updated FROM users;

CREATE PROCEDURE update_last_updated_from_timestamps()
BEGIN
    UPDATE users u
    JOIN last_updated_timestamps t ON u.id = t.user_id
    SET u.last_updated = t.last_updated;
END PROCEDURE;

CREATE EVENT update_last_updated_event
ON SCHEDULE EVERY 1 MINUTE
DO
BEGIN
    CALL update_last_updated_from_timestamps();
END PROCEDURE;