Solved

Oracle: Inserting to a view, using Trigger

Posted on 2009-05-06
14
1,066 Views
Last Modified: 2013-12-18
This is a new ticket but this following ticket:

http://www.experts-exchange.com/Database/Oracle/10.x/Q_24381581.html

can be referenced as well, it will help provide background for the reasoning behind what I'm doing.

Let's say we have table named origtable1 defined with some fields like:

create table origtable1 ( id int, mydate date );

My programs can select the mydate field and convert to integer by doing this:

select (to_number(to_char(mydate, 'J'))-to_number(to_char(to_date('12/31/1899', 'MM/DD/YYYY'),'J'))) from origtable1

(I want to do this because we are migrating Informix to Oracle, and our C programs treat dates like long/int values, # of days since 12/31/1899).

If mydate field contained today's date May 6, 2009, the resulting value would be 39938.

Now my C program will utilize that value and perhaps do some manipulation such as subtract a day from it etc. Let's just say for simplicity sake that we just want to subtract 1 day, thus our new value would be 39937.

To update table origtable1 with this new value I simply type:

update origtable1 set mydate  = to_date('1899-12-31','yyyy-mm-dd')+39937;

******************************
The above all works fine and is how I want to do selecting,inserting,updating Oracle tables in order to minimize the amount of changes in our C programs.

But I want to improve this even further. In the above scenario, I would have to modify all of our C programs' queries that use dates, wrapping them with all these Oracle date functions.

So I create another user (vrd_view) so that this user can create a view in it's own schema like this:

create or replace view origtable1 as
      select id, (to_number(to_char(mydate, 'J'))-to_number(to_char(to_date('12/31/1899', 'MM/DD/YYYY'),'J'))) mydate
     FROM orig_owner.origtable1

(NOTE: take note that the name of my view is the *same* name as the underlying table in another schema, also field name mydate is the same. This is important so our C programs do not have to change)

Now in my C program, if I connect to the db as user vrd_view, and select from origtable1, I am *not* selecting against the actual table orig_owner.origtable1, I'm instead selecting against the view vrd_view.origtable1. Now my queries work fine because I'll be selecting integer date values like they are expecting. I've already tested this and it works beautifully.


My problem now becomes, how do I insert or update the underlying table of the view, with a date being represented as an integer (39938 for eg.)? I cannot update the view with an expression, already tried that:

(as user vrd_view: )

insert into origtable1 values (1, 39938)
ERROR: virtual column not allowed here)

So - can I create a trigger that is attached to my view, so that it converts the # 39938 to the appropriate Oracle date?
Maybe the trigger can perform this type of logic:

-      for each of the date type fields in the table being updated/inserted to:
o      modify the value being updated/inserted to be: to_date('1899-12-31','yyyy-mm-dd')+THEVALUE
o      (where THEVALUE is the new value of the field from the program)

This way it is completely transparent to the C programs and will greatly help me in reducing the manual changes required.

So is creating a trigger to do the above a) possible and b) the best solution?

Thanks!
Karen


0
Comment
Question by:KarenNewton
  • 6
  • 6
  • 2
14 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24317866
Hi Karen, as I pointed out in the other question, you have too many conversions (to_date, to_char, to_number), so first off, lets simplify again, To convert a date in oracle to a number, as you requested, using the test table, is simply:

select mydate - to_date('12/31/1899', 'MM/DD/YYYY') as informixdate
   from orig_owner.origtable1

Date arithmetic results in integers. No need for all those other functions. I am reading the rest of your problem, but wanted to put this at the top, because we discussed this in the other thread.
0
 

Author Comment

by:KarenNewton
ID: 24317962
Hi - yes thanks for showing that to me again! I took some ill advice it seems.. thank goodness it's much simpler to extract the date in the format I want it. I've updated my logic now to use the simplified conversion as you've pointed out.

Looking forward to further replies, might have to leave shortly and not post back here for several hours until I'm online this evening again.

Thanks again,
Karen
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24318047
The challenge here is you are trying to avoid porting your app to Oracle, while porting your data to Oracle. I think this is a bad decision, and a waste of money.

If you want to deal with integers, you COULD just use an integer column instead. Then create a read-only view that does the to_date() calculation when needed, but all of your Informix compliant code can use the original column, and you can use the view for the Oracle DATE output.

Do you have ability to use 11g? If this is a new port, why not go with 11.1 right away? It has calculated columns, but will still not allow you to insert the number and convert on the fly as calculated columns are read-only, you can only update them by changing the underlying column. So a trigger may be your best bet.

I or someone can probably do a sample for you, with a before insert trigger on insert or update of an integer column to populate an date column.
0
 

Author Comment

by:KarenNewton
ID: 24318161
The issue is we have several C programs that are affected by the migration, so I'm just trying to identify the best solution that would equate to the least amount of changes required. Down the road we will undertake several rewrites of said code, so we want to keep modifications now to a minimum. Unfortunately going to 11g probably isn't viable, as we already have licensing for 10g.

So I guess it all boils down to, can my C programs insert dates as type long? I think with a trigger this should be feasible? Could the sql section of the trigger simply do:

:NEW.mydate := to_date('12/31/1899','MM/DD/YYYY') + :NEW.mydate

Thanks again,
Karen
0
 
LVL 47

Expert Comment

by:schwertner
ID: 24318279
Inserting in views?
This can be done DIRECTLY only in simple views.

If your view is not simple you have programmatically code
HOW will you insert in the base table(s).

Oracle provides INSTEAD OF INSERT triggers that can be used.

You have to figure out the way how should be the base tables inserted.
This is not so easy and some times even imposible.
0
 

Author Comment

by:KarenNewton
ID: 24324473
Wouldn't my above code snippet work properly with an INSTEAD OF INSERT trigger? Except the wording of that makes me think that instead of inserting do the following...

:NEW.mydate := to_date('12/31/1899','MM/DD/YYYY') + :NEW.mydate

How then, after doing the above, does one execute the insert?

0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24325018
INSTEAD OF TRIGGER is used to override, so in that case you have to explicitly code the INSERT statements inside the trigger, and usually the trigger is used to INSERT into the base tables. Essentially the VIEW is such that makes it non-updatable, so you write INSTEAD OF trigger to perform the logic behind the scenes.

I don't think you need this, I think you can do it without a view, by just storing your original date column as a INTEGER / NUMBER in Oracle, and adding a 2nd column that is real DATE type, and adding a plain BEFORE INSERT OR UPDATE ... FOR EACH ROW trigger that will convert the INTEGER column to the actual DATE. I will post an actual example as soon as I take care of morning things, like coffee and voicemail.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
ID: 24325585
Definitelly you are speaking about updating of views.
If you would like to update (insert) via the view then you should use INSTEAD OF trigger(s).
this is the thing that makes your view nonupdatable:
 (to_number(to_char(mydate, 'J'))-to_number(to_char(to_date('12/31/1899', 'MM/DD/YYYY'),'J')))

If not - the update the base table directly (no matter where - in the INSTEAD OF trigger or directly):

insert into orig_owner.origtable1 values (1, to_date('12/31/1899','MM/DD/YYYY') + :NEW.mydate);
update  orig_owner.origtable1 set  mydate =  to_date('12/31/1899','MM/DD/YYYY') + :NEW.mydate);


0
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 500 total points
ID: 24325793
To the original problem, which is allowing the legacy code to make use of an Informix 1899 based date.

Here is a quick sample without a view, that just uses a trigger updates back and forth, depending on which column you use. Since this is a shared INSERT + UPDATE trigger, to really make it robust you can also add

IF UPDATING ...

IF INSERTING ...

to handle odd conditions when both columns are provided, etc. I did not test with all combinations, etc.

drop table t;
 

create table t (

  id integer,

  orig_date integer,    -- number of days since 12/31/1899

  ora_date date         -- real DATE, we will update by trigger

);
 

-- To get fancy, you can put reverse trigger on ora_date column, but be aware of what will

-- happen if you update / insert both values
 

-- When updating ora_date

create or replace trigger t_updins

before insert or update on t

for each row

when (new.ora_date is not null or new.orig_date is not null)

begin

  if :new.orig_date is not null and :new.ora_date is null then

     dbms_output.put_line('Updating ora_date from orig_date ' || :new.orig_date);

     :new.ora_date := to_date('12/31/1899','MM/DD/YYYY') + :new.orig_date;

  elsif :new.ora_date is not null then

     dbms_output.put_line('Updating orig_date from ora_date');

     :new.orig_date := :new.ora_date - to_date('12/31/1899','MM/DD/YYYY');

  end if;

end;

/
 
 

-- Some test data

alter session set NLS_DATE_FORMAT = 'MM/DD/YYYY';

set serveroutput on
 

insert into t values(1, 0, null);

insert into t(id, orig_date) values(2, 1);

insert into t(id, orig_date) values(3, 39937);
 

insert into t(id, ora_date) values(4, sysdate);

insert into t(id, ora_date) values(5, sysdate-365);

commit;
 

select * from t;
 

update t set ora_date = sysdate where id = 1;

select * from t where id = 1;
 

commit;

Open in new window

0
 

Author Comment

by:KarenNewton
ID: 24329865
Thank you for all of the replies. User mrjoltcola has definitely put in a lot of time and details to help me out, so it's only deserving I award the points to him.

I've discussed all of this with my dba staff and they think the best solution is to forego using triggers and instead use a function. Meaning we have to modify our C code to wrap the date fields with the Oracle function, that will in turn return to use the numeric value our C programs are expecting.

Thanks again for all of the help, I certainly learned a great deal.

Karen
0
 

Author Closing Comment

by:KarenNewton
ID: 31578595
Thank you again for your detailed replies that were spot on to what I was asking for.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24330093
If you go that route, and expect to use indexes on the column, make sure to create a deterministic function, and create a function based index on the table, or predicates on teh column will not use indexes.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24330129
I meant: "or predicates on the column that make use of the function will not use indexes."
0
 

Author Comment

by:KarenNewton
ID: 24331377
Thanks once again I'll definitely pass that advice along to my dba group so they are aware.

Thank you,
Karen
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now