How to trigger an action without using triggers

Question

I just want to ask. We have a calendar table, let's call it CAL1 in one database, lets call it DB1. CAL1 is manually populated. Now in another database, DB2, there is another calendar table CAL2. What I need to do is to populate CAL2 based on the values from CAL1. I don't know when will CAL1 be populated. There is also some other calendar tables/tables in other databases that will refer to CAL1 so I need to make an API that will populate or at least execute the procedures from the other databases e.g. DB2 whenever new data is loaded to CAL1. I initially think of using triggers but read that it's better to not use it. So is there any other way that I can do this without using trigger? Thanks a lot.

Answer 1

it depends on how up to date you want CAL2 to be.

If it immediately should reflect the values in CAL1 then a trigger is the way to go.

If it can be a few mnutes out of sync then you could have a job that runs every 5 mins and calls a stored procedure that syncs the tables.

Answer 2

Another solution is to create a package that does your CRUD operations on CAL1. When a value is changed, inserted or deleted the procedure that is called can create a job to update CAL2, or insert the values on an advanced queue to be propagated to subscriber databases.

Arwen asks for more details:

There are a number of ways to implement this and the best way must take into account your databases and network.

• If DB2 is across the country then there is the possibility of time outs due to latency or downtime. If DB2 is located on the other side of the server rack you might feel more comfortable assuming if DB1 is up then DB2 will be up.
• You should also consider what are the consequences of DB2 not being updated? Is this mission critical or should be noticed and fixed within 24 hours. A mission critical application requires a more rugged solution.
• how many transactions? tens, thousands, millions per day? Hardware, network choices and coding solution are all affected by this.

Lets start with a sample insert on DB1. This should be part of a package

   FUNCTION CREATE_CASE (case_type_id_in   IN NUMBER,
                         created_by_in     IN NUMBER,
                         file_ref_in       IN VARCHAR2:= NULL,
                         file_title_in     IN VARCHAR2:= NULL)
      RETURN NUMBER
   IS
      /******************************************************************************
      PURPOSE: create a case of any type
      ******************************************************************************/
      c_log_case_created CONSTANT   VARCHAR2 (10) := 'LOG_1009';
      v_case_id                     NUMBER (10);
      v_file_ref                    VARCHAR2 (200);
      v_log_message                 VARCHAR2 (200);
      v_contact_date                TIMESTAMP (6) := CURRENT_TIMESTAMP;
   BEGIN

      SELECT   CASE_ID_SEQ.NEXTVAL INTO v_case_id FROM DUAL;

      INSERT INTO APPBASE.CASE (ID,
                                CASE_TYPE_ID,
                                TITLE,
                                CREATED_BY_USER_ID,
                                LAST_MODIFIED_BY_USER_ID,
                                REFERENCE_NUMBER)
        VALUES   (v_case_id,
                  case_type_id_in,
                  file_title_in,
                  created_by_in,
                  created_by_in,
                  v_file_ref);



      v_log_message := v_file_ref || ',' || v_case_id || ',';
      APP_UTIL.APP_LOG (created_by_in,
                                 v_case_id,
                                 c_log_case_created,
                                 v_log_message);

    --notice: no commit, this is done outside the package and no error handling
    -- we want errors to bubble up to the calling application
    -- but we do want a log of what was successful
    --here is where we add the same information to an oracle advanced queue
    --the most bulletproof solution, allows the main transaction to complete
    --and the secondary insert becomes a second transaction
    QUEUE_UTIL.ADD_FILE_TO_QUEUE(case_type_id_in,v_case_id,null);
      RETURN v_case_id;
   END CREATE_CASE;

       --then in another package QUEUE_UTIL dealing with the queue
   PROCEDURE add_file_to_queue (case_type_in   IN NUMBER,
                                d_case_id_in   IN NUMBER,
                                d_other_in     IN VARCHAR2:= NULL)
   IS
      /******************************************************************************
      PURPOSE: when there is a change to a file (create, closed or reopen)   add the    change to queue of changes 
     ******************************************************************************/
      queue_options        SYS.DBMS_AQ.enqueue_options_t;
      message_properties   SYS.DBMS_AQ.message_properties_t;
      message_id           RAW (16);
      my_message           file_action;
      err_text             VARCHAR2 (2000);
      PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
      my_message := chrcbase.file_action (case_type_in, d_case_id_in, d_other_in);
      DBMS_AQ.enqueue (queue_name           => 'CASE_QUEUE',
                       enqueue_options      => queue_options,
                       message_properties   => message_properties,
                       payload              => my_message,
                       msgid                => message_id);

      IF g_debugging
      THEN
         q$error_manager.TRACE (
            q$error_manager.MAKE_AN_INFO_MSG ('Inserted case ID'),
               'QUEUE_UTIL.ADD_FILE_TO_QUEUE '
            || ' Case '
            || d_case_id_in
            || ' action '
            || action_in
            || ' Other '
            || d_other_in,
            TRUE
         );
      END IF;

      COMMIT;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         err_text := SQLERRM;

         INSERT INTO application_error_logging (
                                                           ID,
                                                           request_uri,
                                                           ERROR_CODE,
                                                           user_id,
                                                           stack_trace,
                                                           information,
                                                           TIMESTAMP
                    )
           VALUES   (
                        application_error_logging_seq.NEXTVAL,
                        'QUEUE_UTIL.add_file_to_queue',
                        NULL,
                        g_admin_id,
                           'Failed to insert case ID '
                        || d_case_id_in
                        || ' file '
                        || ' '
                        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH12:MI'),
                        'Failure:no Data ' || err_text,
                        SYSDATE
                    );

         COMMIT;
      WHEN OTHERS
      THEN
         err_text := SQLERRM;

         INSERT INTO application_error_logging (
                                                           ID,
                                                           request_uri,
                                                           ERROR_CODE,
                                                           user_id,
                                                           stack_trace,
                                                           information,
                                                           TIMESTAMP
                    )
           VALUES   (
                        application_error_logging_seq.NEXTVAL,
                        'QUEUE_UTIL.add_file_to_queue',
                        NULL,
                        g_admin_id,
                           'Failed to insert case ID '
                        || d_case_id_in
                        || ' '
                        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH12:MI'),
                        'Failure:when Others ' || err_text,
                        SYSDATE
                    );

         COMMIT;
   END add_file_to_queue;

This is not a complete solution as you need to create a type for the message and a queue, and grant permissions to manage the queue. The last portion of the solution is to pop the messages off the queue and insert the changes into DB2.

Answer 3

So this is crazy, but how about just having the app layer take care of it? We just had a architectural meeting and discussed how reliable it's been for us.

If you are dealing with something that you need 99.999999% certainty, then yes, a trigger is better. However, if I may throw a number out for illustration, but if 99.9999% is good enough, go with the app layer in my humble opinion.