Home » SQL & PL/SQL » SQL & PL/SQL » help in trigger craetion. (10g)
help in trigger craetion. [message #326600] Thu, 12 June 2008 01:51 Go to next message
appukambha
Messages: 12
Registered: May 2008
Junior Member
Hi all,

I have two tables in my database.1 table holds data of past 7 days other holds data of past 30 days.Table 1 gets updated every minute.Every nite data from table 1 is moved to table 2. But because of this in table 2 one day's data is missing.So, it is being planned that a trigger should be made to move data to table 2 from table 1 every time a row is inserted in table 1.

Can u please tell me how to create trigger so that every time a row is added to table 1 it is added to table 2 as well..

please reply....
Re: help in trigger craetion. [message #326614 is a reply to message #326600] Thu, 12 June 2008 02:11 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
Read This
Re: help in trigger craetion. [message #326616 is a reply to message #326600] Thu, 12 June 2008 02:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you do it in a trigger, the transaction will not complete until the insert in the second table is complete.
Why not use a single table? You can create a view on that returning only data from the last 7 days..
Re: help in trigger creation.--plz help [message #326617 is a reply to message #326600] Thu, 12 June 2008 02:13 Go to previous messageGo to next message
appukambha
Messages: 12
Registered: May 2008
Junior Member


Hi,


I wrote this trigger:

create trigger tigger_name after insert on tab1 for each row

begin

insert into tab2 values(NEW.field1,New.field2,...,NEW.field100)

end

But there are around 100 columns in table 1 ,so wht can I do for this other than including all the 100 fileds in the statement.


please reply
Re: help in trigger craetion. [message #326620 is a reply to message #326600] Thu, 12 June 2008 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You could create a view upon table2 and table1 and use this one instead of table2. It is far much safer.

Regards
Michel
Re: help in trigger craetion. [message #326629 is a reply to message #326620] Thu, 12 June 2008 02:23 Go to previous messageGo to next message
appukambha
Messages: 12
Registered: May 2008
Junior Member


Hi thanx for the reply.....

craeting view is a rite approach but when I suggested the same thing...they said that we need both the tables and so a trigger is to be made to insert into that table2.

please reply....
Re: help in trigger craetion. [message #326634 is a reply to message #326629] Thu, 12 June 2008 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a basic trigger but I strongly suggest you don't do that.

Regards
Michel
Re: help in trigger creation.--plz help [message #326638 is a reply to message #326617] Thu, 12 June 2008 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But there are around 100 columns in table 1 ,so wht can I do for this other than including all the 100 fileds in the statement.

Are you paid to write the less possible?
A trigger is just write once and you can generate it with just a query on user_tab_columns.

Regards
Michel
Re: help in trigger craetion. [message #326641 is a reply to message #326629] Thu, 12 June 2008 02:37 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
appukambha wrote on Thu, 12 June 2008 09:23
craeting view is a rite approach but when I suggested the same thing...they said that we need both the tables and so a trigger is to be made to insert into that table2.

Ask them why.
Previous Topic: SQLs (completed & currently running ) in a session
Next Topic: Problem: Find all table names in db and then find the list of SP used by this table
Goto Forum:
  


Current Time: Fri Dec 09 00:30:58 CST 2016

Total time taken to generate the page: 0.10961 seconds