This discussion is archived
7 Replies Latest reply: Feb 2, 2013 2:13 AM by BillyVerreynne RSS

trigger to increment a non-pk field after insert in another table

988556 Newbie
Currently Being Moderated
I have three tables.
One for projects, one for volunteers, and a bridge entity for the many to many relationship between the Project and Volunteer.

In Project table, I have a field called, Volunteers_currently_signed_up, which means the number of volunteers currently signed up to participate in a project.

When I add an entry to my bridge entity which is composed of Volunteer_ID and Project_ID, I want the Volunteers_currently_signed_up to increment by 1, where the Project_ID in the bridge entity corresponds to that in Project.


I have very very little PL/SQL, and this is my amateur attempt so far:

CREATE OR REPLACE trigger "BI_Volunteers_currently_signed_up"
BEFORE INSERT OR UPDATE ON Volunteers_in_project
for each row
WHERE Volunteers_in_project.Project_ID=Project.Project_ID;
begin
Project.Volunteers_currently_signed_up += 1;
end;
/

Can anyone help me write a trigger that achieves the above
  • 1. Re: trigger to increment a non-pk field after insert in another table
    Vite DBA Pro
    Currently Being Moderated
    Hi,

    One of the principles of relational theory is that you never store a value that can be derived. You will always know how many volunteers there are in a project by doing a count query. Storing this value may make it quicker to retrieve, but in the long run will be more expensive to maintain, and will always leave you open to integrity problems.

    Also, creating database objects with mixed case is something I would consider a sackable offense. ie CREATE OR REPLACE trigger "BI_Volunteers_currently_signed_up"

    Regards
    Andre
  • 2. Re: trigger to increment a non-pk field after insert in another table
    988556 Newbie
    Currently Being Moderated
    So basically, you would deem it a bad idea, and the same information should just be communicated to the user by report based on an sql query?
  • 3. Re: trigger to increment a non-pk field after insert in another table
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    I agree with Andre.

    If you want to have a "running counter" in the data, then any transaction affecting the count needs to update the counter.

    This requires the counter to be locked in order for a transaction to make the change. And released only when that transaction is committed or rolled back.

    The end result? Serialisation. Only ONE transaction at a time can happen as ALL transactions need to get into the queue waiting for a lock on the counter, in order to update it.

    This kills performance and scalability. Annihilates it. Completely. You can have the most powerful computer on this planet, capable of doing a billion transactions every second. But with your approach and design, it will crawl along at the speed of only being able to process a single transaction at a time.

    This approach is exactly NOT how one designs multi-user and multi-process systems. Serialisation has a very heavy penalty. And needs to be avoided.
  • 4. Re: trigger to increment a non-pk field after insert in another table
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    985553 wrote:
    So basically, you would deem it a bad idea, and the same information should just be communicated to the user by report based on an sql query?
    Bad idea? A bit of an understatement. ;-)

    If report performance is an issue (millions of rows to process to get count), then a fast refresh materialised view (aggregation that includes the counts) can be considered. The Oracle Data Warehouse guide covers this in detail.
  • 5. Re: trigger to increment a non-pk field after insert in another table
    988556 Newbie
    Currently Being Moderated
    okay, I see what you guys mean. I won't do that so.

    Thanks for replying
  • 6. Re: trigger to increment a non-pk field after insert in another table
    988573 Newbie
    Currently Being Moderated
    Hi,

    This is Sanjit here. I analyzed your thread. if I am correct, you are referring to update your volunteers_signed_up
    column to by 1 every time a record is inserted into the Bridge_Entity table, Right?
    According to your requirements, I am providing you a Trigger that will resolve your issue.


    CREATE OR REPLACE trigger BI_Volts_cuntly_signed_up
    BEFORE INSERT OR UPDATE ON Bridge_entity
    for each row
    declare
    v_signed_up number;
    begin
    select nvl(max(Volunteers_currently_signed_up),0) into v_signed_up from volunteers
    where project_id=:new.project_id;
    v_signed_up:=v_signed_up+1;
    update volunteers set Volunteers_currently_signed_up=v_signed_up where project_id=:new.project_id;
    end ;

    Here, Bridge_entity and volunteers are two different tables and the primary key is applied on the
    Projects table and foreign keys are referenced to these two tables.
    In the beginning, Volunteers_currently_signed_up column will be null inserted for every project_id inserted into the
    volunteers table. Later, execute this trigger. This trigger will automatically update the Volunteers_currently_signed_up
    column each time a new record will be inserted into the Bridge_Entity table.
    Hope this will work 4 you.
    Thanks.
    Sanjit
  • 7. Re: trigger to increment a non-pk field after insert in another table
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    You run this SQL:
    select nvl(max(Volunteers_currently_signed_up),0) into v_signed_up from volunteers where project_id=:new.project_id;
    This code is not thread-safe.

    Between that select statement and the following statement:
    v_signed_up:=v_signed_up+1;
    ..other transactions (same project) could have committed and changed the signed up volunteer total. Which means your calculate-the-count statement is now using the wrong total.

    To get the correct total means locking all rows in order to do the count and update the total, preventing any other transaction from changing the answer you are using in the update. This is called serialisation. Serialisation kills performance.

    This is basic database stuff. Get it wrong and your approach and code corrupt valuable business data, and results in business decisions and reports based on data that is wrong.

Legend

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