{question}
What is the best way to determine a table or view creation date?
{question}
{answer}
NuoDB offers two ways to determine the Date that a table or view was created:
- Use DDL-audit logging. For more information, click here.
- Use a trigger on an 'after creating a table' event. The trigger will insert to a table that you keep a DDL audit. The DDL-audit table may include the following columns: object_name, schema_name, and the creation_date.
Here is an example:
create table audit_log ( TABLENAME string, SCHEMA string, TYPE varchar (16), CREATION_DATE DATE);
SET DELIMITER @
CREATE TRIGGER ddl_trigger_after_create_table ON DATABASE AFTER CREATE TABLE AS
INSERT INTO audit_log VALUES (DDL_OBJECT_NAME, DDL_SCHEMA_NAME, DDL_OPERATION_TYPE, now());
END_TRIGGER;
@
Test it:
create table tab1 (ID number, description string);
select * from audit_log;
TABLENAME SCHEMA TYPE CREATION_DATE
---------- ------- ------------ --------------
TAB1 USER create_table 2020-04-10
If you have any questions, please reach out by clicking here.
{answer}
Comments