TABLE LOGGING

ALTER TABLE tablename LOGGING tablename
ALTER TABLE tablename NOLOGGING

These forms of ALTER TABLE enable and disable TABLE LOGGING. They are only available for SQL databases.

Using table logging a log of changes to rows in one table can be made automatically to another table. This can be used in auditing or just reporting changes to a table. The table under investigation, the master table, is created in the normal way with CREATE TABLE. The logging table is also created with CREATE TABLE, perhaps with REVOKE UPDATE,DELETE and with some column names that match the master table. When matching column names the IPREFIXs are disregarded. The two tables are paired-up using

ALTER TABLE master_table LOGGING TO logging_table

Changes to the master table's columns that appear in the logging table, trigger an INSERT into the logging table. For UPDATE and DELETE the master table's columns before the change are recorded. For INSERT the master table's columns after the change are recorded.

The type of modification (INSERT/UPDATE/DELETE) can be recorded in any table by adding an INT(1) column and setting it in the CREATE TABLE statement's TYPESTAMP yyy clause. The values for this column come from the KDB_LOG_TYPE_ENUM enumeration and are _KDB_LOG_TYPE_INSERT, _KDB_LOG_TYPE_UPDATE and _KDB_LOG_TYPE_DELETE. Clearly _KDB_LOG_TYPE_DELETE can only appear in the logging table.

The user, time-stamp, type-stamp and serial columns can be copied from the master table in the normal way by having columns of the same name. The user, time-stamp, type-stamp and serial columns of the logging table, as defined in the logging tables's CREATE TABLE ... USERSTAMP www, TIMESTAMP xxx, TYPESTAMP yyy, SERIAL A zzz, are filled in correctly, so these should have different names to the master table.

While a table is marked as part of a logging pair RENAME TABLE and DROP TABLE are illegal. You can see if a table is part of a logging pair with KI_INFO, and KI_DESCRIBE_SCHEMA gives you the name of the other table in the pair.

When creating the logging table's indexes be aware that indexes have to be unique. Perhaps adding an auto-serial column to the indexes would help.

This function is only available for SQL databases. Although KDB tracks the ALTER TABLE link, KI_INFO and KI_DESCRIBE_SCHEMA work and KDB errors RENAME TABLE and DROP TABLE, the actual logging is not supported.