管理mysql的触发器

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

• MySQL Triggers Implementation

原文链接：http://outofmemory.cn/mysql/trigger/managing-trigger-in-mysql