📋

Description

This error indicates an attempt to create a database trigger with a name that already exists within the same schema or database. MariaDB requires trigger names to be unique, and this error typically occurs when a `CREATE TRIGGER` statement is executed for an already defined trigger. It prevents the new trigger from being created, halting the database operation.

💬

Error Message

Trigger already exists

🔍

Known Causes

3 known causes

⚠️

Schema Script Re-execution

Executing a database schema script or migration that includes a `CREATE TRIGGER` statement for a trigger that was already successfully created in a previous run.

⚠️

Flawed Deployment Logic

Automated deployment tools or manual processes attempting to create a trigger without first checking for its existence or idempotently handling its creation.

⚠️

Manual Creation Oversight

A user manually executing a `CREATE TRIGGER` statement for a trigger name that has already been defined on the database, leading to a naming conflict.

🛠️

Solutions

3 solutions available

1. Identify and Drop Existing Trigger easy

Find the trigger that's causing the conflict and remove it before attempting creation.

1

Connect to your MariaDB instance.

mysql -u your_user -p

2

Use the `SHOW TRIGGERS` command to list all triggers in the current database or specify a table.

SHOW TRIGGERS;

3

Alternatively, query the `information_schema.TRIGGERS` table for more detailed information and to pinpoint the exact trigger.

SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = 'your_trigger_name' AND EVENT_OBJECT_TABLE = 'your_table_name';

4

Once identified, drop the existing trigger using the `DROP TRIGGER` statement.

DROP TRIGGER IF EXISTS your_trigger_name;

5

Now, attempt to create your trigger again.

-- Your CREATE TRIGGER statement here

2. Conditional Trigger Creation medium

Prevent the error by checking if the trigger already exists before attempting to create it.

1

Connect to your MariaDB instance.

mysql -u your_user -p

2

Use a `CREATE TRIGGER ... IF NOT EXISTS` statement. This is the most robust way to handle potential duplicate creations.

CREATE TRIGGER IF NOT EXISTS your_trigger_name
BEFORE INSERT ON your_table_name
FOR EACH ROW
BEGIN
    -- Trigger logic here
END;

3

If you are unable to use `IF NOT EXISTS` (e.g., older MariaDB versions or specific scripting scenarios), you can manually check for existence before creating.

SELECT COUNT(*) INTO @trigger_exists FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = 'your_trigger_name' AND EVENT_OBJECT_TABLE = 'your_table_name';

IF @trigger_exists = 0 THEN
    CREATE TRIGGER your_trigger_name
    BEFORE INSERT ON your_table_name
    FOR EACH ROW
    BEGIN
        -- Trigger logic here
    END;
END IF;

3. Review Deployment Scripts medium

Ensure your database deployment scripts don't contain duplicate trigger definitions.

1

Locate the SQL script responsible for creating your triggers. This might be part of your application's setup or migration scripts.

2

Carefully examine the script for any instances where the same trigger is defined more than once for the same table and event (INSERT, UPDATE, DELETE).

3

Remove any duplicate `CREATE TRIGGER` statements. If you need to ensure idempotency, use the `IF NOT EXISTS` clause as shown in Solution 2.

4

Re-run your deployment script after cleaning it up.

🔗

Related Errors

5 related errors

MariaDB Error 1359: Duplicate Trigger Creation