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!

What does 'for each row' means when inserting a new row?

Discussion in 'Oracle Apps Technical' started by Cynthia Clare, May 22, 2018.

  1. Cynthia Clare

    Cynthia Clare Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    USA
    Hello,

    I'm trying to comprehend the logic behind Triggers in SQL.

    I understand that, when updating some value, 'for each row' means that the Trigger will be executed for each row affected by the change (the update operation).

    But for Inserts I don't understand how the 'for each row' statement works.

    If I have a trigger to run before the insert and, based on the values of the inserted row, decide whether to insert it or not, why is 'for each row' necessary here? And why do I need it to use the :new value.

    An example:

    create or replace trigger tgr_example
    before insert on tbl_example
    for each row
    declare
    ValueToHigh exception;
    begin
    if:)new.value > 50) then
    raise ValueToHigh;
    end if;
    exception
    when ValueTohigh then
    DBMS_OUTPUT.PUT_LINE('To High.');
    end;

    So, why is it required the 'for each row' statement for this example?

    Thanks!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,637
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're checking the value in each potential row to see if it matches or exceeds the desired criteria. A 'bulk' trigger can't work in that situation as it will miss specific values the need to be checked. Yes, it's an insert but a 'create table ... as select ...' is a bulk insert and only a 'for each row' trigger can be used to verify the data prior to insert.

    You can't think of inserts as single-statement, single-row operations.
     
    Cynthia Clare likes this.