There is a simple way to find out if a table has been updated recently. Just check the table information_schema.tables. Here is the layout:
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
EXAMPLE : You have a table called mydata.mytable and you want to see if that table was updated in the last 10 minutes. Here is the simple query to tell you:
SELECT COUNT(1) UpdatedCount FROM information_schema.tables
WHERE table_schema = 'mydata'
AND table_name = 'mytable'
AND update_time > ( NOW() - INTERVAL 10 MINUTE );
It will give you either 0 (Was not updated) or 1 (Was Updated).
The update_time of the table should be updated before anyone locks it, especially if the table you are polling is MyISAM. InnoDB is another story. Given InnoDB's transactional nature, you could poll the timestamps in the table rather that information_schema.tables just in case information_schema.tables does not update as quickly as desired. Just make sure the table's timestamp column is indexed.
There is an alternative outside of MySQL but does not work for InnoDB tables configured with innodb_file_per_table disabled.
EXAMPLE : Data is installed in default datadir (var/lib/mysql) and you want to poll the table mydata.mytable.
For mydata.mytable being MyISAM
TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.MYD | awk '{print $8}'`
For mydata.mytable being InnoDB
TIMESTMP=`ls -l /var/lib/mysql/mydata/mytable.ibd | awk '{print $8}'`
Of course, you would shell script the time comparison to see if is is less than 10 minutes or whatever interval you want.
With regard to events, you will be OK if you use CREATE EVENT but make sure you do not update InnoDB tables in an event. I went through heartburn helping someone troubleshoot this in a chat room on a past question.
Give it a Try !!!
Aha! I've found the answer. Talk about an edge case.
First, I found this post from 2007, where someone says:
...the AFTER trigger for synchronizing the CTXCAT index on [column] is
not firing (since my update statement does not include the indexed
column).
...
Oracle, would it not be better to generate the CTXCAT
trigger to examine the :old and :new values in the indexed column,
rather than using a check on (if updating([column]))?
Over a year later, someone replied:
I fixed this issue by adding additional UPDATING (first_name and
last_name) conditions in the DR$table_name trigger.
Here is part of the modified trigger.
if (inserting or updating('LAST_NAME_FIRST') or updating('FIRST_NAME')
or updating('LAST_NAME')) then reindex := TRUE;
Hm, so the CTXCAT index uses a trigger to know when it needs to update the index for a particular entry. All I need to do is tweak the trigger and recompile it so it does what I want.
To get the content of the trigger:
SELECT text
FROM user_source
WHERE name = 'DR$NAMES_IDXTC'
AND type = 'TRIGGER'
ORDER BY line;
I copied this into Sublime, prettified it, and found this (simplified):
TRIGGER "TEST"."DR$NAMES_IDXTC" AFTER
INSERT
OR
UPDATE ON "TEST"."NAMES"
FOR EACH ROW DECLARE REINDEX boolean := FALSE;
BEGIN
IF (inserting
OR updating('COMPOUND_NAME')
OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME") THEN REINDEX := TRUE;
END IF;
...
END;
You can see that the 12c Oracle Text version's AFTER trigger does actually compare the :new
and :old
values of the indexed column to see if it needs to update--not the case back in 2008.
So...if I'm updating the :new
value in my BEFORE trigger, that should be reflected in the AFTER trigger, and the comparison would kick off an update to the index. What gives?
Well, here are the two SQL statements I was using:
update test.names set
first_name = 'Skye',
last_name = 'Fillingim'
where ... ;
update test.names set
first_name = null,
last_name = null
where ... ;
(Slightly different than what I said in my question, I apologize.)
The effect here is that, each time I used one of these statements, either the :old.compound_name
or :new.compound_name
would be null. So when we get to this condition:
OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"
We are doing an equality comparison against a null, which evaluates to UNKNOWN. Hence, no index update.
This is actually an extreme edge case, because you have to be indirectly modifying :new.column
via a trigger, and either :new
or :old
must be null. I would never have discovered it it I hadn't used those exact SQL statements.
So, we have a slight update to the trigger:
IF (inserting
OR updating('COMPOUND_NAME')
OR :new."COMPOUND_NAME" <> :old."COMPOUND_NAME"
OR (:new."COMPOUND_NAME" IS NULL AND :old."COMPOUND_NAME" IS NOT NULL)
OR (:new."COMPOUND_NAME" IS NOT NULL AND :old."COMPOUND_NAME" IS NULL)
) THEN REINDEX := TRUE;
And then just stick CREATE OR REPLACE
in front of the old trigger, recompile it, and everything works perfectly.
Best Answer
Why do you want to log DDL on your DB? just for auditing? a trigger is highly not recommended because that if you have an error you can't even disable the trigger. consider this scenario:
I suggest you'll take a look at Oracle DB vault and audit vault http://docs.oracle.com/cd/E14472_01/doc.102/e14459/toc.htm
It got some really nice features for monitoring and controlling administration commands