The following trigger is used to track changes to documents. There is a document table and a journal table. As changes are made to the document table, the trigger changes the journal table. A separate application processes and removes rows from the journal table. The trigger ensures that there is at most one row in the journal table for each corresponding row in the document table. So, if a document is changed more than once, then the trigger maintains a single journal row for that document.
CREATE TABLE document_table ( docnum INTEGER, document BLOB) UNIQUE PRIMARY INDEX(docnum); CREATE TABLE document_journal_table ( docnum INTEGER, action CHARARACTER(1)) UNIQUE PRIMARY INDEX(docnum); CREATE TRIGGER log_inserts_to_doc AFTER INSERT ON document_table REFERENCING new AS new_row FOR EACH ROW BEGIN ATOMIC CALL insert_doc_journal( new_row.docnum ); END; CREATE PROCEDURE insert_doc_journal(IN docnum INTEGER) BEGIN DECLARE num_delete_journal_records INTEGER; SELECT COUNT(docnum) INTO :num_delete_journal_records FROM document_journal_table WHERE docnum=:docnum AND action = 'D'; IF num_delete_journal_records > 0 THEN -- We have a delete journal record. So, this document -- was previously deleted and now they are inserting it. -- A delete followed by an insert is really an update so -- remove the delete journal record and add an update -- change the journal record UPDATE document_journal_table SET action = 'U' WHERE docnum = :docnum; ELSE -- We either have no journal records or we have an insert or -- update record. An insert followed by an insert is still an -- insert. An insert followed by an update is really an insert -- so remove any journal record and add an insert journal -- record. UPDATE document_journal_table SET action = 'I' WHERE docnum = :docnum; ELSE INSERT INTO document_journal_table VALUES (:docnum,'I'); END IF; END;