1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Regarding trigger

Discussion in 'SQL PL/SQL' started by vjohnny8, Jul 19, 2011.

  1. vjohnny8

    vjohnny8 Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone,

    There will be no gap concept in the dates as given below. it will take the current rate of the max date that is currently present in the database i.e

    14-jul-11 2.5
    13-jul-11 3.5 5.6

    the 2 above rows are currently present in the database, now if a new row is inserted as below,

    date curr_rate prev_rate
    17-jul-11 1.5 2.5

    then the previous rate that is shown for 17th jul, is the current rate of 14th jul ( which is already present in the database)

    so in simple terms, the previous rate of 17 jul should display the value of the current rate of the max date already present in the database.

    how can i modify the below trigger to achieve this ?

    create table t1
    ( index_id number,
    time_vertex number,
    date1 date,
    date_rate number(10,2),
    prev_rate number(10,2) )

    create or replace trigger t1_ai before insert on t1 for each row
    begin
    begin
    Select date_rate
    into :new.prev_rate
    from t1
    where rowid in
    (select rn from
    (select rowid rn, dense_rank() over (partition by index_id,time_vertex order by date1 desc) rnk from t1
    where index_id = :new.index_id and
    time_vertex = :new.time_vertex and
    date1 < :new.date1
    ) where rnk =1 );

    exception when no_data_found then
    :new.prev_rate := -1;
    end;
    end;
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where are the index_Id and time_vertex values?
     
  3. krisgopala.k

    krisgopala.k Active Member

    Messages:
    2
    Likes Received:
    1
    Trophy Points:
    85
    Without knowing the relevance of index_id and time_vertex, it is difficult to say. But otherwise, isn't it the same as
    select max(date_rate) from t1 where date1 =
    (select max(date1) from t1 )
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Code (SQL):

    15:46:30 SQL> SELECT * FROM TEST_TAB ORDER BY INDEX_ID,DATE_D;

      INDEX_ID DATE_D    CURR_RATE_N PREV_RATE_N
    ---------- --------- ----------- -----------
           100 13-JUL-11         3.5         5.6
           100 14-JUL-11         2.5         3.5

    15:47:08 SQL> INSERT INTO DMIG.TEST_TAB
    15:47:32   2  (INDEX_ID,DATE_D, CURR_RATE_N)
    15:47:38   3  VALUES
    15:47:42   4  (100,TO_DATE('07/17/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5);

    1 ROW created.

    15:47:48 SQL> commit;

    Commit complete.

    15:47:55 SQL> SELECT * FROM TEST_TAB ORDER BY INDEX_ID,DATE_D;

      INDEX_ID DATE_D    CURR_RATE_N PREV_RATE_N
    ---------- --------- ----------- -----------
           100 13-JUL-11         3.5         5.6
           100 14-JUL-11         2.5         3.5
           100 17-JUL-11           5         2.5

    15:48:01 SQL> INSERT INTO DMIG.TEST_TAB
       (INDEX_ID,DATE_D, CURR_RATE_N)
     VALUES
      (100,TO_DATE('07/20/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),1.7);15:48:57   2  15:48:57   3  15:48:57   4

    1 ROW created.

    15:48:58 SQL> SELECT * FROM TEST_TAB ORDER BY INDEX_ID,DATE_D;

      INDEX_ID DATE_D    CURR_RATE_N PREV_RATE_N
    ---------- --------- ----------- -----------
           100 13-JUL-11         3.5         5.6
           100 14-JUL-11         2.5         3.5
           100 17-JUL-11           5         2.5
           100 20-JUL-11         1.7           5

    15:49:02 SQL>