row level triggers


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: row level triggers

  1. #1
    Ervin Rodriguez Guest

    row level triggers


    I have a table with 10 columns. I want to write a row level trigger that
    will trigger whenever a row is inserted into my table. This trigger will
    then take the values of the 10 columns and pass it to a procedure.

    how can i code that?

    also, if two rows are inserted almost at the same time, what is the best
    way so that my trigger takes both rows and pass them to my procedure instead
    of calling my procedure twice(one for each row inserted).

  2. #2
    Boris Milrud Guest

    Re: row level triggers


    Ervin,

    Here is a sample of the code for row-level trigger you asked:

    create or replace trigger MyTable_AIR
    after insert on MyTable
    for each row

    begin
    MyProcedure(:new.Column1,
    :new.Column2,
    ...
    :new.Column10);

    end MyTable_AIR;
    /

    If you want to accomplish the same task for multiple rows with only one call,
    then you would need to implement following:
    1. Your function has to accept collection(s) of values as parameters, i.e.
    PL/SQL table, nested table or varray.

    2. You have to use combination of row-level trigger, table-level trigger
    and package with collection(s) declared.

    3. For each row, row-level trigger will fire and add newly inserted data
    to the, let's say, PL/SQL table(s) defined inside your collection. For example,
    you could define LoadNewData() procedure inside your package that would have
    the same parameters as your old procedure and load data into 10 PL/SQL tables.
    1 element will be added to each of 10 PL/SQL tables.

    Let's say you inserted 100 rows. Row-level trigger fired 100 times and now
    you have 10 PL/SQL tables (one for each column) with 100 elements each.

    4. Now table-level trigger fires and call another procedure of the same package,
    i.e. ProcessNewData(), which will take your 10 PL/SQL tables as a parameters
    for processing. At the end, you have to reset your PL/SQL tables for the
    next event. You may create another procedure, i.e. ResetTables(), in the
    same package to make it clean.

    Let me know if you understand the algorithm and know how to code all of this.

    Boris Milrud.


    "Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >
    >I have a table with 10 columns. I want to write a row level trigger that
    >will trigger whenever a row is inserted into my table. This trigger will
    >then take the values of the 10 columns and pass it to a procedure.
    >
    >how can i code that?
    >
    >also, if two rows are inserted almost at the same time, what is the best
    >way so that my trigger takes both rows and pass them to my procedure instead
    >of calling my procedure twice(one for each row inserted).



  3. #3
    Ervin Rodriguez Guest

    Re: row level triggers


    Thank you for your help Mr. Milrud. Your solution is understandable. I have
    3 questions. How do I implement table-level trigger. can you give me some
    sample code.

    also, suppose i insert a row and the row level trigger executes
    and pass this values to the function Process_new_data. now the table in
    in this function contain one element. While this data is being process,
    I insert another row and Process_new_data is call. will the table in Process_new_data
    contain 2 records or Is this a different process running?

    I want to implement the following:
    suppose a row is inserted and rowlevel trigger is fired. then this data
    will be process by calling Process_new_data function. so my table in Process_new_data
    function will have the following:

    1. ervin rodriguez 3456 ... .... 99

    this data will be process. while processing i insert another row in my table.
    so the trigger will stored this value which in term will call Process_new_data
    again. my question is: does my table in Process_new_data will contain the
    followig?

    1. ervin rodriguez 3456 ... .... 99
    2. mia joe 345 .. .....788


    If this is the case, i will like to have a for loop that will go
    until the end of the table and process all the day.
    I want to to this because that way the procedure can determine
    if new data is on the table while processing the first record.
    This way oracle doesn't have to fired different processes for each
    row. It will be nice to keep appending data in the pl/sql table.

    The last question is: suppose i have 3 data records in my pl/sql table.
    when they get process my procedure will call my reset_table function. during
    that time i insert another row. will this cause problem?
    how can i be sure while cleaning up, in coming data are going to be in my
    pl/sql table? thank you for your help

    "Boris Milrud" <milrud@hotmail.com> wrote:
    >
    >Ervin,
    >
    >Here is a sample of the code for row-level trigger you asked:
    >
    >create or replace trigger MyTable_AIR
    >after insert on MyTable
    >for each row
    >
    >begin
    > MyProcedure(:new.Column1,
    > :new.Column2,
    > ...
    > :new.Column10);
    >
    >end MyTable_AIR;
    >/
    >
    >If you want to accomplish the same task for multiple rows with only one

    call,
    >then you would need to implement following:
    >1. Your function has to accept collection(s) of values as parameters, i.e.
    >PL/SQL table, nested table or varray.
    >
    >2. You have to use combination of row-level trigger, table-level trigger
    >and package with collection(s) declared.
    >
    >3. For each row, row-level trigger will fire and add newly inserted data
    >to the, let's say, PL/SQL table(s) defined inside your collection. For example,
    >you could define LoadNewData() procedure inside your package that would

    have
    >the same parameters as your old procedure and load data into 10 PL/SQL tables.
    >1 element will be added to each of 10 PL/SQL tables.
    >
    >Let's say you inserted 100 rows. Row-level trigger fired 100 times and now
    >you have 10 PL/SQL tables (one for each column) with 100 elements each.
    >
    >4. Now table-level trigger fires and call another procedure of the same

    package,
    >i.e. ProcessNewData(), which will take your 10 PL/SQL tables as a parameters
    >for processing. At the end, you have to reset your PL/SQL tables for the
    >next event. You may create another procedure, i.e. ResetTables(), in the
    >same package to make it clean.
    >
    >Let me know if you understand the algorithm and know how to code all of

    this.
    >
    >Boris Milrud.
    >
    >
    >"Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >>
    >>I have a table with 10 columns. I want to write a row level trigger that
    >>will trigger whenever a row is inserted into my table. This trigger will
    >>then take the values of the 10 columns and pass it to a procedure.
    >>
    >>how can i code that?
    >>
    >>also, if two rows are inserted almost at the same time, what is the best
    >>way so that my trigger takes both rows and pass them to my procedure instead
    >>of calling my procedure twice(one for each row inserted).

    >



  4. #4
    Ervin Guest

    Re: row level triggers


    Ervin,

    Your post contradicts to your original one. If you want to process one-row
    data in your function Process_new_data immediately after insert, then you
    don't need a combination of table-level trigger and PL/SQL table for storing
    this information.

    Here is your scenario:
    <<I want to implement the following: suppose a row is inserted and rowlevel
    trigger is fired. Then this data will be process by calling Process_new_data
    function...>>
    If that's what you want, then call Process_new_data function from your row-level
    trigger, and forget about PL/SQL table.

    In any case, syntax for creation of table-level trigger is the same as row-level
    except it does not have FOR EACH ROW clause.

    Boris Milrud.

    "Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >
    >Thank you for your help Mr. Milrud. Your solution is understandable. I

    have
    >3 questions. How do I implement table-level trigger. can you give me some
    >sample code.
    >
    >also, suppose i insert a row and the row level trigger executes
    >and pass this values to the function Process_new_data. now the table in
    >in this function contain one element. While this data is being process,
    >I insert another row and Process_new_data is call. will the table in Process_new_data
    >contain 2 records or Is this a different process running?
    >
    >I want to implement the following:
    >suppose a row is inserted and rowlevel trigger is fired. then this data
    >will be process by calling Process_new_data function. so my table in Process_new_data
    >function will have the following:
    >
    >1. ervin rodriguez 3456 ... .... 99
    >
    >this data will be process. while processing i insert another row in my table.
    >so the trigger will stored this value which in term will call Process_new_data
    >again. my question is: does my table in Process_new_data will contain

    the
    >followig?
    >
    >1. ervin rodriguez 3456 ... .... 99
    >2. mia joe 345 .. .....788
    >
    >
    >If this is the case, i will like to have a for loop that will go
    >until the end of the table and process all the day.
    >I want to to this because that way the procedure can determine
    >if new data is on the table while processing the first record.
    >This way oracle doesn't have to fired different processes for each
    >row. It will be nice to keep appending data in the pl/sql table.
    >
    >The last question is: suppose i have 3 data records in my pl/sql table.
    > when they get process my procedure will call my reset_table function.

    during
    >that time i insert another row. will this cause problem?
    >how can i be sure while cleaning up, in coming data are going to be in my
    >pl/sql table? thank you for your help
    >
    >"Boris Milrud" <milrud@hotmail.com> wrote:
    >>
    >>Ervin,
    >>
    >>Here is a sample of the code for row-level trigger you asked:
    >>
    >>create or replace trigger MyTable_AIR
    >>after insert on MyTable
    >>for each row
    >>
    >>begin
    >> MyProcedure(:new.Column1,
    >> :new.Column2,
    >> ...
    >> :new.Column10);
    >>
    >>end MyTable_AIR;
    >>/
    >>
    >>If you want to accomplish the same task for multiple rows with only one

    >call,
    >>then you would need to implement following:
    >>1. Your function has to accept collection(s) of values as parameters, i.e.
    >>PL/SQL table, nested table or varray.
    >>
    >>2. You have to use combination of row-level trigger, table-level trigger
    >>and package with collection(s) declared.
    >>
    >>3. For each row, row-level trigger will fire and add newly inserted data
    >>to the, let's say, PL/SQL table(s) defined inside your collection. For

    example,
    >>you could define LoadNewData() procedure inside your package that would

    >have
    >>the same parameters as your old procedure and load data into 10 PL/SQL

    tables.
    >>1 element will be added to each of 10 PL/SQL tables.
    >>
    >>Let's say you inserted 100 rows. Row-level trigger fired 100 times and

    now
    >>you have 10 PL/SQL tables (one for each column) with 100 elements each.
    >>
    >>4. Now table-level trigger fires and call another procedure of the same

    >package,
    >>i.e. ProcessNewData(), which will take your 10 PL/SQL tables as a parameters
    >>for processing. At the end, you have to reset your PL/SQL tables for the
    >>next event. You may create another procedure, i.e. ResetTables(), in the
    >>same package to make it clean.
    >>
    >>Let me know if you understand the algorithm and know how to code all of

    >this.
    >>
    >>Boris Milrud.
    >>
    >>
    >>"Ervin Rodriguez" <ervin06@netscape.net> wrote:
    >>>
    >>>I have a table with 10 columns. I want to write a row level trigger that
    >>>will trigger whenever a row is inserted into my table. This trigger will
    >>>then take the values of the 10 columns and pass it to a procedure.
    >>>
    >>>how can i code that?
    >>>
    >>>also, if two rows are inserted almost at the same time, what is the best
    >>>way so that my trigger takes both rows and pass them to my procedure instead
    >>>of calling my procedure twice(one for each row inserted).

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center