Once created trigger associated with a table, you can view the trigger by going directly to the folder which contains the trigger. Trigger is stored as plain text file in the database folder as follows: /data_folder/database_name/table_name.trg, with any plain text editor such as notepad you can view it. MySQL provides you another way to view the trigger by executing SQL statement:
SELECT * FROM Information_Schema.Trigger
WHERE Trigger_schema = 'database_name' AND
Trigger_name = 'trigger_name';
In this method you are not only view the content of the trigger but also other metadata associating with it such as table name, definer (name of MySQL who created the trigger).
If you want to retrieve all triggers associated with a database just executing the following SQL statement:
SELECT * FROM Information_Schema.Trigger
WHERE Trigger_schema = 'database_name';
To find all triggers associating with a database table, just executing the following SQL statement:
SELECT * FROM Information_Schema.Trigger
WHERE Trigger_schema = 'database_name' AND
Event_object_table = 'table_name';
In MySQL you are not only able to view the trigger but also remove an existing trigger. To remove a trigger you can use the SQL statement DROP TRIGGER as follows:
DROP TRIGGER table_name.trigger_name
For example if you want to drop trigger before_employees_update which 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. MySQL doesn't provide you SQL statement to alter an existing trigger like altering other database objects such as tables or stored procedures.
In this tutorial, you've learn how to view and retrieve information related to a trigger. You've also learnt how to delete an existing trigger by using DROP TRIGGER statement.