20.4.1.Creating Text Triggers
The CREATE TEXT TRIGGER statement creates a set of tables and procedures named after the table and column being watched. The TT_QUERY_<xx> table contains the set of queries, the TT_HIT_<xx> table records the matches and the TT_USER_<xx> table can be used to map stored queries to specific users that should be notified.
Syntax:
CREATE TEXT TRIGGER ON <table> [(<data_column>)] DROP TEXT TRIGGER ON <table> [(<data_column>)]
The <data_column> is optional and must be a text indexed column.
The table to be watched by a text trigger should have a free text indexed column. This may or may not be XML data.
The <table> must be text indexed before creating text trigger on it.
Example20.5.Creating a Text Trigger
create table ftt (id integer not null primary key, dt long varchar); create text xml index on ftt (dt); create text trigger on ftt;
Adding queries to the text trigger
TT_QUERY_ftt ('virtuoso and server and international',
1, 'Virtuoso international support', 'iam@foo.bar');
this adds a query which will filter only documents matching words 'virtuoso', 'server' and 'international', named 'Virtuoso international support' of the user with Id equal to 1 with e-mail notification.
TT_XPATH_QUERY_ftt ('/chapter[@label = ''XI'']',
2, 'Chapter XI changes', '');
this will add an XPATH query which will filter only XML documents matching Chapter XI, named 'Chapter XI changes' of the user with Id equal to 2 without e-mail notification.
insert into ftt values (1, 'virtuoso server international'); insert into ftt values (2, 'virtuoso international'); select TTH_D_ID, TTH_T_ID, TTH_U_ID, TTH_TS from ftt_dt_HIT;
will produce
TTH_U_ID TTH_D_ID TTH_T_ID TTH_TS _____________________________________________ 1 1 1 2001-01-17 12:35:30
Meaning that row from 'ftt' with id equal to 1 matches query with TT_ID equal to 1 defined for user with ID equal to 1. Also the hit is registered on '2001-01-17 12:35:30'.