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.


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.

Example 20.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'.