Re: Stored Procedures and Tablespaces

From: James Arvigo <Hooper_X_at_Spam_Rage.com>
Date: Wed, 29 Jul 1998 11:08:04 -0500
Message-ID: <35BF48E4.F74AB2CA_at_Spam_Rage.com>

Svein,

Hi there. Although MotoX and Mike Cox have already answered you appropriately above, I thought I'd send you a quick little example of how I am doing this exact thing in our database system here. In this example, each time a record in our "RADIUS" table gets updated, I copy a few of its fields over to another table called "ACCOUNTING".

Both tables, in my case are in the same tablespace and owned by the same user ID, however, when you read about how to create triggers you will see that by simply prefacing the table name with the user ID that owns it, you can span either user ID's or tablespaces.

This is an "On Update - For Each Row - Trigger".

---

CREATE OR REPLACE TRIGGER radius_au_rtrg AFTER UPDATE ON radius
FOR EACH ROW
WHEN (new.acct_status_type = 'Stop' AND old.acct_status_type = 'Start') BEGIN
   INSERT INTO accounting VALUES (

:new.user_name,

      TO_DATE(:new.start_time, 'DY Mon DD HH24:MI:SS YYYY'),
      TO_DATE(:new.stop_time, 'DY Mon DD HH24:MI:SS YYYY'),


      :new.called_station_id,

      :new.calling_station_id,

      :new.acct_session_id,

      :new.acct_session_time,

      :new.ascend_presession_time,

      :new.ascend_connect_progress,


      :new.ascend_disconnect_cause,


      (:new.ascend_presession_time + :new.acct_session_time));

END radius_au_rtrg;

---

Note, I named the trigger: "radius_au_rtrg" This is meant to indicate that this logic unit is active upon the "radius" table,
that it is of type "after update = au", for each row = "r", trigger = "trg".

Hope that hleps, Good luck!

--
James Arvigo

============================================================
* SQL Server & Oracle DBA
* Software & Intranet Developer
* Thrifty Call, Inc. via The Maxim Group
* Austin, Texas
*----------------------------------------------------------
* EMAILS:
* Work: JamesA  -AT- ThriftyCall.com
* Home: JArvigo -AT- Hotmail.com
*
* ( Sorry I had obfuscate the emails... *sigh* )
============================================================

  Svein Ormel wrote:

> What I'm trying to do is creating to sets of tables, each in it's own
> tablespace (lets call them cactus and emms).
> The tables in the cactus tablespace is updated from an external application,
> and each time this happens I would like to extract these new values and
> insert them into the tables in the emms tablespace. These new values will
> then in turn be used by another application, so what I'm really trying to do
> is to create a datalink between two applications.

Received on Wed Jul 29 1998 - 18:08:04 CEST