Summary: in this tutorial, you will learn how to manage triggers including listing, modifying and removing triggers in MySQL databases.
After creating a trigger, you can display its definition in the data folder, which contains trigger definition file. A trigger is stored as plain text file in the following database folder:
/data_folder/database_name/table_name.trg
MySQL provides you with an alternative way to display the trigger by using the following SQL statement:
SELECT * FROM Information_Schema.Triggers WHERE Trigger_schema = 'database_name' AND Trigger_name = 'trigger_name';
The statement allows you to view both content of the trigger and its metadata such as associated table name and definer (name of MySQL user who created the trigger).
If you want to retrieve all triggers in a particular database, just executing the following SQL statement:
SELECT * FROM Information_Schema.Triggers WHERE Trigger_schema = 'database_name';
To find all triggers associated with a particular table, you use the following query:
SELECT * FROM Information_Schema.Triggers WHERE Trigger_schema = 'database_name' AND Event_object_table = 'table_name';
MySQL Drop Trigger
In MySQL, you can not only to view the trigger but also remove an existing one. To remove a trigger, you use DROP TRIGGER statement as follows:
DROP TRIGGER table_name.trigger_name
For example, if you want to remove before_employees_update trigger associated with the table employees, you can perform the following query:
DROP TRIGGER employees.before_employees_update
To modify a trigger, you have to delete it first and recreate it with the new code. There is no such ALTER TRIGGERstatement exists in MySQL, and you cannot modify an existing trigger like modifying other database objects such as tables and stored procedures.
In this tutorial, you have learned how to manage triggers in MySQL. You have also learned how to remove an existing trigger using the DROP TRIGGER statement.
Related Tutorials
原文链接：http://outofmemory.cn/mysql/trigger/managing-trigger-in-mysql
随时随地看视频
