Every few weeks the database guy at work backs up the production database and then several weeks later imports it into the development database. I don't know why it takes so long between backup and restore, but anything I do in the database during that time period is lost unless I explicitly tell him to save it. Not having every little change memorized has at least once caused a data loss issue in one of my applications. So my plan is to create a table and populate it with information about the changes made to the database so I have an answer next time he asks me "what changes have you made to the database since September 5th?"
I'm trying to compose this trigger to log structural changes to a table that I will query with a cron job and backup the relevant tables as needed. I don't care about data, just structural changes.
How do I access the variables needed to populate the table in this statement?
CREATE OR REPLACE TRIGGER DB_CHANGES_T AFTER ALTER OR CREATE OR DROP OR GRANT OR RENAME OR TRUNCATE ON DATABASE BEGIN insert into db_change_log ( event_type, -- 'create', 'alter', 'drop', 'grant', 'rename', or 'truncate' affected_entity, -- the name of the table/view/package/etc that was affected entity_type, -- 'table', 'view', 'index', 'package', 'procedure', 'function', etc, etc event_time ) values (?, ? , ?, sysdate); END;