Call-Back Trigger

 

--Assume you have many application servers running EHCache. You need to make
--sure that these caches have the latest data from the database. Doing a
--total cache refresh on a periodic basis is not feasible because there
--is a large amount of data and only a small percentage of it gets updated.

--Instead, you want the database to send the latest data to the
--caches upon updating corresponding database data.

Create Or Replace Trigger Call_back
   Before Update Of Prod_limit, Current_prod_cnt
   On Prod_total
   Referencing New As New Old As Old
   For Each Row
   When(New.Current_prod_cnt >= New.Prod_limit)
Declare
   Pragma Autonomous_transaction;
   L_web_page     Clob;
   Err_sqlerrm    Varchar2(150);
   L_host_name    Qsn_app.App_server.Host_name%Type;
   L_port_name    Qsn_app.App_server.Port_name%Type;
   L_uri_string   Varchar2(1999);
   L_xml_string   Varchar2(1999);
Begin
   -- Build the url string which includes the primary key and new data
   -- The jsp will know what to do when it gets this URL data
   -- Obviously ,your url must match the specification
   -- that your Java programmer give you.
   L_xml_string    :=
         '/proj1-admin/manageCache_new.jsp?'
      || 'action=update&cacheName=com.myco.product.'
      || 'prod.common.entity.ProdKey&cacheKey='
      || :New.Prod_total_key
      || '&cacheData='
      || '<?xml version = 1.0><ROWSET><ROW num=1?><PROD_LIMIT>'
      || :New.Prod_limit
      || '</PROD_LIMIT><CURRENT_PROD_CNT>'
      || :New.Current_prod_cnt
      || '</CURRENT_PROD_CNT></ROW></ROWSET>';
    -- if the web page does not come back within 5 seconds, stop waiting
   -- and report the problem
   UTL_HTTP.Set_transfer_timeout(5);

    -- app_servers is a table with the names of hosts which are hosting the
   -- caches which need to be updated.
   For Cur1 In (Select Host_name, Port_name
                  From App_servers)
   Loop
      -- assign variables to scope outside of loop so
      -- exception handling can use them
      L_host_name     := Cur1.Host_name;
      L_port_name     := Cur1.Port_name;
      -- prefix your URL with the Protocal, HOST, PORT that you are currently
      -- going to call
      L_uri_string    :=
         Utl_url.Escape(   'http://'
                        || Cur1.Host_name
                        || ':'
                        || Cur1.Port_name
                        || L_xml_string);

      -- Send the data via HTTP GET to the target server
      Select Httpuritype.Createuri(L_uri_string).Getclob() Web_page
        Into L_web_page
        From DUAL;

      -- inspect the resulting web page to see if things went good or bad
      -- if the HTTP call timed-out, (more than 5 seconds),
      -- l_web_page will be null
      --
      If INSTR(L_web_page, 'SUCCESS') = 0
      Then
         My_util.Dautonomous_exception
                        (Pprogramname          => 'call_back',
                         Pprogramlocation      => 'cache refresh failed ',
                         Perrormsg             => 'error',
                         Pmsg_line1            =>    'ProductKey = '
                                                  || :New.Prod_total_key,
                         Pmsg_line2            =>    'Host_name = '
                                                  || L_host_name,
                         Pmsg_line3            =>    'Port_name = '
                                                  || L_port_name,
                         Pmsg_line4            => SUBSTR(L_web_page,
                                                         1,
                                                         200));
      End If;
   End Loop;
Exception
   When Others
   Then
      Err_sqlerrm    := SUBSTR(SQLERRM, 1, 150);
      My_util.Dautonomous_exception
                                (Pprogramname          => 'call_back',
                                 Pprogramlocation      => 'Serious Error',
                                 Perrormsg             => Err_sqlerrm,
                                 Pmsg_line1            =>    'ProductKey = '
                                                          || :New.Prod_total_key,
                                 Pmsg_line2            =>    'Host_name = '
                                                          || L_host_name,
                                 Pmsg_line3            =>    'Port_name = '
                                                          || L_port_name,
                                 Pmsg_line4            => Null);
End Call_back;
/