Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Please explain why exactly you don't want to use triggers -- they seem exactly the right solution for this task. –  Heinzi Oct 25 '12 at 9:26
2  
It is good practice to not add complex business logic in triggers but in this case I should say that triggers is the way to go. –  Peter Åkerlund Oct 25 '12 at 9:54
1  
I'd go for teh triggers that way you will never miss a change. Whoever told you not to use triggers for such a task is sadly misinformed, this is exaclty what triggers are for. I would be careful though if the other database is on a differnt server. And make sure to test your trigger both with single records insertions and multiple record insertions. –  HLGEM Oct 25 '12 at 13:33
    
My boss is not comfortable with triggers and actually, this is my first time to use it, if i will take that direction that's why Im trying to weigh all the options. –  Arwen Oct 29 '12 at 8:34
    
Which DBMS are you talking about? –  a_horse_with_no_name Oct 30 '12 at 8:46

3 Answers 3

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.

share|improve this answer
    
This is how I first think of, have a job that runs every 5 mins but my boss won't go for it and yet, wants everything to be updated or informed of the change as soon as the CAL1 has been updated. –  Arwen Oct 29 '12 at 8:35

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.

share|improve this answer
    
+1 For more than two tables the queue would probably be best, but with just two tables, the package could insert/update/delete both tables directly. –  Leigh Riffel Oct 25 '12 at 12:24
    
This sounds good but how to do it? –  Arwen Oct 29 '12 at 8:36
1  
@Arwen the answer is a good basis for what you need to do. Kevin is not going to write the rest of the code for you. –  Phil Oct 29 '12 at 14:24
    
@Kevinsky, thanks for taking time to write the code. –  Arwen Oct 30 '12 at 2:48
    
@Phil, Im not expecting a code but just an explanation on how the queue works since I haven't used it so I have zero knowledge about it. –  Arwen Oct 30 '12 at 2:49

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.