Re: Trigger on information_schema.routines
Posted by: Devart Team
Date: December 24, 2009 09:25AM

There is no way to associate trigger with view or with system table (mysql.proc).

But system view information_schema.routines has LAST_ALTERED (DATETAME) field, you could use this to check modifications. For example -

1. Create additional table and fill it with routines -
CREATE TABLE routines_copy SELECT
ROUTINE_SCHEMA, ROUTINE_NAME, LAST_ALTERED
FROM
information_schema.ROUTINES;

2. Modify some routines.

3. Call this query to get list of changed routines -
SELECT * FROM routines_copy c
JOIN information_schema.ROUTINES r ON r.ROUTINE_SCHEMA = c.ROUTINE_SCHEMA AND r.ROUTINE_NAME = c.ROUTINE_NAME AND r.LAST_ALTERED <> c.LAST_ALTERED

4. Repopulate routines_copy table. This function can be called periodically.

Devart Company,
MySQL management tools
http://www.devart.com/dbforge/mysql/

Options: ReplyQuote


Subject
Views
Written By
Posted
10466
December 24, 2009 06:30AM
Re: Trigger on information_schema.routines
5102
December 24, 2009 09:25AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.