Query to DROP mysql triggers dynamically

Question

Can we delete set of triggers in mysql by passing an array to DROP TRIGGERS query from PHP?

$triggersToDelete = ['trigger1', 'trigger2', 'trigger6'];

$sql = 'DROP TRIGGER IF EXISTS $triggersToDelete';

Is this possible? Or any better way we can achieve this? Thanks in advance.

Aleksa Nikolic · Accepted Answer · 2018-05-07 10:58:17Z

1

Something like this would do the job.

<?php

$host = "localhost";
$user = "root";
$pass = "";
$database ="dbname";

$conn = mysqli_connect($host, $user, $pass, $database);

if (!$conn) {
    die("Failed to connect to the database");
}

$triggersToDelete = ["trigger1", "trigger2", "trigger6"];

foreach ($triggersToDelete as $trigger) {
    if (!mysqli_query($conn, "DROP TRIGGER IF EXISTS " . htmlspecialchars($trigger, ENT_QUOTES))) {
        echo "Failed to delete trigger: " . $trigger . "<br>";
    }
}

mysqli_close($conn);

Connect to the database, execute drop trigger for each trigger in array, close database connection

answered May 7, 2018 at 10:58

Aleksa Nikolic

1255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Devika V Over a year ago

Thank you for the response. But it query the DB for every trigger right? Can we drop set of triggers in a single database query. so that it will call DB only once.

Aleksa Nikolic Over a year ago

Unfortunately no, MySQL doesn't support dropping multiple triggers with one query

Collectives™ on Stack Overflow

Query to DROP mysql triggers dynamically

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related