MariaDB Drop Trigger

Summary: in this tutorial, you will learn how to use the MariaDB drop trigger statement to drop a trigger from a database.

Introduction to MariaDB drop trigger statement

The drop trigger statement deletes a trigger from a database. The following shows the syntax of the drop trigger statement:

drop trigger [if exists] trigger_name;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the trigger that you want to delete after the drop trigger keywords.
• Second, use the optional if exists clause to conditionally drop a trigger only if it exists.

If you drop a non-existing trigger without the if exists clause, MariaDB will issue an error. However, if you use the if exists clause, MariaDB will issue a NOTE.

To execute the drop trigger statement successfully, you need to have the trigger privilege for the table associated with the trigger.

When you drop a table, MariaDB automatically removes all triggers associated with the dropped table.

MariaDB drop trigger example

First, create a new trigger associated with the country_stats table:

delimiter $$

create trigger before_stat_update
    before update
    on country_stats for each row
begin
    if new.population > old.population * 2 then
        signal sqlstate '45000' 
            set message_text='Error! the new population is too high.';
    end if;
end$$

delimiter ;
Code language: SQL (Structured Query Language) (sql)

Second, show the triggers:

show triggers;
Code language: SQL (Structured Query Language) (sql)

Third, drop the trigger before_stat_update:

drop trigger if exists before_stat_update;
Code language: SQL (Structured Query Language) (sql)

Finally, verify the deletion by showing the triggers again:

show triggers;
Code language: SQL (Structured Query Language) (sql)

As you can see from the output, the trigger before_stat_update has been dropped.

In this tutorial, you have learned how to use the MariaDB drop trigger statement to drop a trigger from the database.