Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Posts
    81

    Unanswered: counts in a table

    I seek the possibility for an automatic update of a certain column from a counted value. In my example I have two columns (object and counts). I want to update the column 'counts' with the result of the count of records with the identical value in the column 'object'

    Example:
    create table HOUSE
    (object varchar2(20),
    counts number);

    insert into HOUSE (object) values ('WINDOW');
    insert into HOUSE (object) values ('WINDOW');
    insert into HOUSE (object) values ('DOOR') ;
    insert into HOUSE (object) values ('WINDOW');

    I seek the syntax of a INSERT- and UPDATE-trigger like

    create trigger HOUSE_UPDATE
    after update on HOUSE
    for each row
    begin
    update HOUSE set counts = (count * from HOUSE where object = :new.object);
    end;

    So that the results of the table should be:

    WINDOW, 3
    WINDOW, 3
    DOOR, 1
    WINDOW, 3

    If I make an update like
    'update HOUSE set object = DOOR where rownum = 2' the the result of the complete table should be:

    WINDOW, 2
    DOOR, 2
    DOOR, 2
    WINDOW, 2

    How do I do that?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >If I make an update like
    >'update HOUSE set object = DOOR where rownum = 2'
    The statement above will NEVER update any rows; 'cuz ROWNUM will never = 2.
    >How do I do that?
    Therefore based upon above, You don't do that.

    Please do NOT post hamework assignments in the future.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2003
    Posts
    81
    I was just using the rownum to illustrate that I was just updating one single row - and that I want to update the other two records by using triggers. This is an example - not the real table which include unique columns I can use for the update.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    The main question here is: WHY?
    Why on Earth do you want to store this dynamic (and redundand) information in a table?

    You can retrieve it with a query:
    SELECT object, COUNT(*) OVER (PARTITION BY object) counts
    FROM house;
    If you insist on accessing it like a table column, create a view from the above query.

  5. #5
    Join Date
    Jun 2003
    Posts
    81
    Thanks for the hint, but I still need some help. I have now made a view based on your hint. I have also made two triggers since I still need the possibility for making updates and inserts on the data. But my triggers fails because of the contents of the view.
    Last edited by kfc@vd.dk; 03-15-07 at 05:54.

  6. #6
    Join Date
    Jun 2003
    Posts
    81
    Quote Originally Posted by kfc@vd.dk
    CREATE OR REPLACE VIEW HOUSE_VIEW
    (OBJECT, ID, COUNTS)
    AS
    SELECT
    object,
    id ,
    COUNT(*) OVER (PARTITION BY object) counts
    FROM house
    /

    CREATE OR REPLACE TRIGGER house_ins1 instead of insert on house_view
    for each row
    begin
    insert into house
    (object,
    id
    )
    values
    (:new.object,
    :new.id
    );
    end;
    /

    CREATE OR REPLACE TRIGGER house_upd1 instead of update on house_view
    for each row
    begin
    update house set
    object = :new.object,
    id = :new.id
    where id = ld.id;
    end;
    /
    I get a strange error. When I use TOAD for update, I get the Oracle-error message:

    ORA-02014 cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.

    But if I make the update by a SQL-statement in SQL Worksheet, everything works fine.

    By the way: How do I input my SQL as PHP Code?

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    TOAD is an interactive tool, so when you start changes on row, it probably locks it by SELECT FOR UPDATE to block other sessions from making changes on that row.
    As the view contains column with analytic function, it results in this error.
    I am afraid, there is no workaround for this behaviour.

    This leads me to another WHY?
    Why are you making changes on the VIEW, especially by SQL statements?
    Use base table for inserts/updates/deletes and view only for queries containing counts column. The two triggers would be useless then.

    By the way, I never worked with PHP, so I cannot advice you. Maybe you should start on http://www.oracle.com/technology/tech/php/index.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •