This discussion is archived
10 Replies Latest reply: Dec 7, 2012 10:23 AM by stratmo RSS

How to create trigger for counting number inside 3 tables?

Bao Tian Newbie
Currently Being Moderated
Hello I've got a question abour creating triggers.
I'm using sqldeveloper.

I have three tables: people, employee, customer.
People is the generalization of the two other tables, so you can find p_id as primary key
for all tables. That means there are 7 people -> 3 employees + 4 customers.

Now I want to create a trigger which always counts the number of the people from the 3 tables,
after new data has been inserted or data has been deleted.
These values have to be saved in a new table which is called "statistics" like this

table statistics
table name | counted_value
--------------------------
people     |
employee |
customer |


What are the next steps to create this trigger?

CREATE TRIGGER Tgr_statistics
AFTER INSERT ON People, Employee, Customer
FOR EACH ROW

BEGINN
UPDATE counted_value on statistics
SET ???????
.
.
.
End;
  • 1. Re: How to create trigger for counting number inside 3 tables?
    sb92075 Guru
    Currently Being Moderated
    DB2000 wrote:
    Hello I've got a question abour creating triggers.
    I'm using sqldeveloper.

    I have three tables: people, employee, customer.
    People is the generalization of the two other tables, so you can find p_id as primary key
    for all tables. That means there are 7 people -> 3 employees + 4 customers.

    Now I want to create a trigger which always counts the number of the people from the 3 tables,
    after new data has been inserted or data has been deleted.
    These values have to be saved in a new table which is called "statistics" like this

    table statistics
    table name | counted_value
    --------------------------
    people     |
    employee |
    customer |


    What are the next steps to create this trigger?

    CREATE TRIGGER Tgr_statistics
    AFTER INSERT ON People, Employee, Customer
    FOR EACH ROW

    BEGINN
    UPDATE counted_value on statistics
    SET ???????
    .
    .
    .
    End;
    It is not best practice to store computed value within table column.
    It is Best Practice to compute the value in real time when that value is required.
  • 2. Re: How to create trigger for counting number inside 3 tables?
    6363 Guru
    Currently Being Moderated
    DB2000 wrote:

    What are the next steps to create this trigger?
    Aside from the fact that storing a row count is non-relational and does not scale, if you really want to store a row count then the next steps would be to delete the trigger code and create a materialized view instead.
  • 3. Re: How to create trigger for counting number inside 3 tables?
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    DB2000 wrote:
    Hello I've got a question abour creating triggers.
    I'm using sqldeveloper.

    I have three tables: people, employee, customer.
    People is the generalization of the two other tables, so you can find p_id as primary key
    for all tables. That means there are 7 people -> 3 employees + 4 customers.
    Why do you need 3 separate tables? Why not 1 table, with a column that indicates if the row represents a customer or an employee (or both, if employees can be customers)?
    Now I want to create a trigger which always counts the number of the people from the 3 tables,
    after new data has been inserted or data has been deleted.
    These values have to be saved in a new table which is called "statistics" like this

    table statistics
    table name | counted_value
    --------------------------
    people     |
    employee |
    customer |


    What are the next steps to create this trigger?
    Step 1 is think very carefully about why you want this, and do you really need to store the numbers. In general, it sounds like you should just compute the COUNTs when you need them, and not try to store the numbers in a table. Is there something special about your application that makes you think another table would help?
    CREATE TRIGGER Tgr_statistics
    AFTER INSERT ON People, Employee, Customer
    You need a separate trigger for each table.
    FOR EACH ROW

    BEGINN
    BEGIN has only 1 N.
    UPDATE counted_value on statistics
    SET ???????
    ...
    SET     counted_value  = counted_value + 1
    WHERE   table_name     = 'PEOPLE';
    .
    .
    .
    End;
    Likewise, decrement counted_value when you DELETE.
    Watch out for DDL commands, like TRUNCATE TABLE and DROP TABLE.
  • 4. Re: How to create trigger for counting number inside 3 tables?
    rp0428 Guru
    Currently Being Moderated
    >
    Now I want to create a trigger which always counts the number of the people from the 3 tables,
    after new data has been inserted or data has been deleted.
    >
    As others have said don't use a trigger for those purposes. Use a materialized view or a stored procedure.

    If you were using a trigger you would use an AFTER trigger not an AFTER ROW trigger. And you would need to have a WHEN DELETING clause.
  • 5. Re: How to create trigger for counting number inside 3 tables?
    Bao Tian Newbie
    Currently Being Moderated
    Frank Kulash wrote:
    Hi,
    Why do you need 3 separate tables? Why not 1 table, with a column that indicates if the row represents a customer or an employee (or both, if employees can be customers)?
    Because employee and customer are specialized, that means every table has additional attributes which are not the same.
    They only use the people_id of table people in order to use general attributes like name, surname etc are .

    Step 1 is think very carefully about why you want this, and do you really need to store the numbers. In general, it sounds like you should just compute the COUNTs when you need them, and not try to store the numbers in a table. Is there something special about your application that makes you think another table would help?
    It's an assigment of a company to have a table statistics where you can find the counts of the 3 different tables together.
    So that the result will be like this:

    table name | count
    ----------------------------
    Person 7
    Employee 3
    Customer 4
  • 6. Re: How to create trigger for counting number inside 3 tables?
    stratmo Newbie
    Currently Being Moderated
    Hi,

    I don't want to bother you all. But I can't see why a view (not a materialized one) does the trick.
    Create Or Replace view statistics_vw
    as
    select 'people' as table_name, count(p_id) as counted_value from People 
    union all
    select 'employee' as table_name, Count(p_id) as counted_value from Employees
    union all
    Select 'customer' as table_name, Count(p_Id) as counted_value From Customer;
    
    -- untested!!!
    I would prefer this solution regarding to the materialized one, because changes will be seen right after commit and there are no additional DML-statements taking place in triggers because they are not needed anymore.
    Do you want to use a statistic-table in order to be able to perform flashback queries on your statistic-table? Then you can use the above view to actualize the statistics table with a trigger for each table. These triggers should keep the the contents of view and table congruent using a simple UPDATE-statement after the statistic-table has been initiated by an insert like:
    -- drop table statistic;
    Create Table Statistic(
       Table_Name        Varchar2(30)
       , Counted_Value   Number
    );
    
    Insert Into Statistic
    select * from Statistic_vw;
    The update in the triggers look something like this:
    Update Statistic Stats
    Set stats.Counted_Value = (Select help_view.Counted_Value 
                         From Statistic_Vw Help_View
                         Where Help_View.Table_Name = Stats.Table_Name
                         )
    Where Exists ( Select 1 From Statistic_Vw
                   Where Help_View.Table_Name = Stats.Table_Name);
    All of the code is untested. If you could provide me your DDL and DML. I would like to test the upper code.

    Please tell me/us if you need a history of the statistic (flashback-query). As has been said probably you don't even need a "hard"object like a table.

    Bye

    stratmo
  • 7. Re: How to create trigger for counting number inside 3 tables?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    DB2000 wrote:
    Hello I've got a question abour creating triggers.
    The answer is Don't.

    Triggers have very little relevance in the modern database.
    Now I want to create a trigger which always counts the number of the people from the 3 tables,
    Wrong. This will impact performance and severely constrain performance.

    A database is a multi-processing and multi-user system. NEVER assume your process and your code and your transaction is the only thing that is happening on the database. Ever. (else you should be using dBASE or MS-Access, and not Oracle).

    A 1000 users can perform a 1000 transactions on a table at the same time - THAT is a reasonable and valid assumption. If you now design each and every transaction on that table to update one specific row in another table - that means a 1000 transactions all attempting to update the same row in the same table.

    This means serialisation. And instead of Oracle being able to deal with a 1000 transactions at the same time, your "clever" trigger now forces Oracle to only be able to deal with a single measly transaction at a time. And then you will likely blame bad performance on Oracle?

    SERIALISATION KILLS PERFORMANCE AND SCALABILITY.

    So be very careful of taking what seems an easy route in database design - slapping some trigger on a table to "do stuff". It is not invariable wrong. It will put serious hurt on the database's ability to effectively do what it is there to do - process data.
  • 8. Re: How to create trigger for counting number inside 3 tables?
    Bao Tian Newbie
    Currently Being Moderated
    stratmo wrote:
    Update Statistic Stats
    Set stats.Counted_Value = (Select help_view.Counted_Value 
    From Statistic_Vw Help_View
    Where Help_View.Table_Name = Stats.Table_Name
    )
    Where Exists ( Select 1 From Statistic_Vw
    Where Help_View.Table_Name = Stats.Table_Name);
    Ah, nice it's woking but when I add a new entry to person table and insert new attributes to my customer
    table then I won't find the new numbers im my table statistics.

    It's strange because the table view shows all results but the statistic table hasn't updated proper, only the number of "Person"
    has been updated.

    I thought after insert or delete or update on people, customer, employee would help but it doesn't work.

    create or replace trigger Trg_Stat
    after insert or delete or update on people
    begin
    update statistic set quantity= (select statistic_view.quantity
    from statistic_view
    where statistck_view.table_name = statistic.table)
    where exists ( select * from statistic_view
    where statistic_view.table = statistik.table);
    end;
  • 9. Re: How to create trigger for counting number inside 3 tables?
    sb92075 Guru
    Currently Being Moderated
    DB2000 wrote:
    stratmo wrote:
    Update Statistic Stats
    Set stats.Counted_Value = (Select help_view.Counted_Value 
    From Statistic_Vw Help_View
    Where Help_View.Table_Name = Stats.Table_Name
    )
    Where Exists ( Select 1 From Statistic_Vw
    Where Help_View.Table_Name = Stats.Table_Name);
    Ah, nice it's woking but when I add a new entry to person table and insert new attributes to my customer
    table then I won't find the new numbers im my table statistics.

    It's strange because the table view shows all results but the statistic table hasn't updated proper, only the number of "Person"
    has been updated.

    I thought after insert or delete or update on people, customer, employee would help but it doesn't work.

    create or replace trigger Trg_Stat
    after insert or delete or update on people
    begin
    update statistic set quantity= (select statistic_view.quantity
    from statistic_view
    where statistck_view.table_name = statistic.table)
    where exists ( select * from statistic_view
    where statistic_view.table = statistik.table);
    end;
    was COMMIT ever issued?
  • 10. Re: How to create trigger for counting number inside 3 tables?
    stratmo Newbie
    Currently Being Moderated
    Hi DB2000,

    reading the above recommendations you should rather use a materialized view with the "refresh fast on commit"-option. Drawbacks of this approach are:

    1. The option "Advanced replication" must be activated. I don't know if this is active in your environment. I myself can only work on ORACLE XE and in this environment it's not activated and probably can't be.
    2. A materialized view with the above option does not support distributed transactions.
    3. A materialized view of such type doesn't allow object-types.
    4. Commits after DML-Statements concerning the underlying objects might take longer.

    @Forum: I myself have no experience with materialized views, so your opinions would be appreciated. Is this solution better/worse than the one using triggers? Are there alternative solutions for this problem.

    Bye

    stratmo

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points