Variables in Oracle Triggers

oracletrigger

Context

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?"

Question

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;

Best Answer

It really sounds like you have an XY Problem.

Your Business Requirement can be summed up with this statement:

I don't care about data, just structural changes.

I'm sure the Business Requirement to capture and manage Schema deltas is part of DevOps. There are solutions that exist for this part of the entire DevOps process. (eg Liquibase) I suggest you don't reinvent the wheel.

Oracle Tools for Schema Deltas

The latest version of Oracle SQLcl (v19.2.1 - $0) has incorporated Liquibase. This allows you to create schema deltas (and save the result in a Code Repository)

The third example in the documentation appears to be a good starting point for your needs.

  1. capture base Schema
  2. capture changes
  3. (example applies base Schema to new Schema)
  4. apply changes to base Schema

Make sure you keep the deltas in a Code Repository.

More Info?

There are a lot of articles that discuss how automate schema changes...especially with the context of DevOps. Feel free to google for more information.

Related Question