7 Replies Latest reply: Dec 11, 2012 10:19 AM by Stew Ashton RSS

    Class Project - Help with triggers

    979118
      Im currently developing a practice database based on a scenario about a boat rental business for a class. I am having trouble creating a trigger that will charge a 75$ late fee for every day late or refund 20$ for every day returned early.
      --Create Charter table-----------------------------------------------
      
      Create Table Charter (
      
      Charter_ID     Number(10)  PRIMARY KEY,
      
      Date_Rent     Date, 
      
      Date_Due     Date, 
      
      Date_Return    Date,
      
      Sup_ID             Number(5),
      
      Equip_ID             Number(10),
      
      Boat_ID              Varchar2(10),
      
      Cus_ID          Varchar2(10),
      
      Disp_ID          Varchar2(10));
      
      ----Creat table customer---------------------
      CREATE TABLE Customer (
      
      Cus_ID       Varchar2(10) PRIMARY KEY,
      
      Cus_FName       Varchar2(20),
      
      Cus_LName       Varchar2(20),
      
      Cus_City       Varchar2(15),
      
      Prev_Bal              Number(10),  
      
      Curr_Bal       Number(10),
      
      Total_Bal        Number(10));
      
      -- TRIGGER 2 to charge &75 late fee or refund $20 for early return 
      
      Create or replace Trigger Rental_Fee_AU
      
      After Update on Charter
      
      For each row
      
      Declare
      
      Amount Number(4);
      
      Day    Number(4);
      
      DueDate Date;
      
      ReturnDate Date;
      
      Begin
      
      DueDate:=:Old.Date_Due;
      
      ReturnDate:=:New.Date_Return;
      
      Day :=ReturnDate-DueDate;
      
      If Day>0 then
      
      Amount :=Day*75;
      
      Elsif Day < 0 then
      
      Amount :=Day*20;
      
      Else
      
      Amount :=Amount;
      
      End if;
      
      
      
      --Update balance
      
      Update Customer
      
      Set Curr_Bal = Amount
      
      Where :Old.Cus_ID=Cus_ID;
      
      
      
      --Update balance
      
      Update Customer
      
      Set Total_Bal = Curr_Bal + Prev_bal
      
      Where :Old.Cus_ID=Cus_ID;
      
      
      
      
      
      --Update Availability
      
      Update Boat
      
      Set Boat_AVAILABILITY = 'Y'
      
      Where Boat.Boat_ID=:New.Boat_ID;
      
      End;
      
      /
      
      show error;
      Here is the code for the triggers and tables involved. I can post the entire script file if needed but it is long and I was not sure about forum etiquette on this site.

      thanks in advance for the help
        • 1. Re: Class Project - Help with triggers
          Hoek
          Welcome to the forum.

          The etiquette is explained here:
          {message:id=9360002}
          (so: post your database version and CREATE TABLE and (a few) INSERT INTO statements and full error codes/messages if you get any.
          The complete SQL and PL/SQL FAQ is here: https://forums.oracle.com/forums/ann.jspa?annID=1535
          • 2. Re: Class Project - Help with triggers
            Frank Kulash
            Hi,

            Welcoem to the forum!
            976115 wrote:
            Im currently developing a practice database based on a scenario about a boat rental business for a class. I am having trouble creating a trigger that will charge a 75$ late fee for every day late or refund 20$ for every day returned early.
            --Create Charter table-----------------------------------------------
            
            Create Table Charter (
            
            Charter_ID     Number(10)  PRIMARY KEY,
            
            Date_Rent     Date, 
            
            Date_Due     Date, 
            
            Date_Return    Date,
            
            Sup_ID             Number(5),
            
            Equip_ID             Number(10),
            
            Boat_ID              Varchar2(10),
            
            Cus_ID          Varchar2(10),
            
            Disp_ID          Varchar2(10));
            
            ----Creat table customer---------------------
            CREATE TABLE Customer (
            
            Cus_ID       Varchar2(10) PRIMARY KEY,
            
            Cus_FName       Varchar2(20),
            
            Cus_LName       Varchar2(20),
            
            Cus_City       Varchar2(15),
            
            Prev_Bal              Number(10),  
            
            Curr_Bal       Number(10),
            
            Total_Bal        Number(10));
            Do you think the double-spacing makes this code easier to read and understand? I'd find it much better if it was single-spaced. In the trigger, there will be times when an occasional blank line improves readability, but not after every single line of code.
            -- TRIGGER 2 to charge &75 late fee or refund $20 for early return 
            
            Create or replace Trigger Rental_Fee_AU
            
            After Update on Charter
            
            For each row
            
            Declare
            
            Amount Number(4);
            
            Day    Number(4);
            
            DueDate Date;
            
            ReturnDate Date;
            
            Begin
            
            DueDate:=:Old.Date_Due;
            
            ReturnDate:=:New.Date_Return;
            
            Day :=ReturnDate-DueDate;
            I would lose the local variables DueDate and ReturnDate, and simply say
            Day := :NEW.returnDate - :NEW.dueDate;
            I find the extra local variables confusing. But then, I won't be maintaining your code. If you really like them, they're not really harming anything.
            If Day>0 then
            
            Amount :=Day*75;
            
            Elsif Day < 0 then
            
            Amount :=Day*20;
            If there's a $20 per day credit , shouldn't you multiply by negative 20?
            Else
            
            Amount :=Amount;
            x := x; never does anything.
            What do you want amount to be at this point? When you get here, you've never assigned a value to amount.
            End if;
            
            
            
            --Update balance
            
            Update Customer
            
            Set Curr_Bal = Amount
            
            Where :Old.Cus_ID=Cus_ID;
            
            
            
            --Update balance
            
            Update Customer
            
            Set Total_Bal = Curr_Bal + Prev_bal
            
            Where :Old.Cus_ID=Cus_ID;
            It would be more efficient to UPDATE both columns of customer at the same time:
            UPDATE  Customer
            SET         Curr_Bal     = Amount
            ,     Total_Bal     = Amount + Prev_bal
            WHERE     Cus_ID          = :Old.Cus_ID;
            --Update Availability
            
            Update Boat
            
            Set Boat_AVAILABILITY = 'Y'
            
            Where Boat.Boat_ID=:New.Boat_ID;
            
            End;
            
            /
            
            show error;
            Here is the code for the triggers and tables involved. I can post the entire script file if needed but it is long and I was not sure about forum etiquette on this site.
            Yes, always post a complete test script that people can run to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for all tables as they exist at some point in time. Also post some DML statements on charter, and the contents of all the tables after each one.
            You can leave off the tables and columns that don't play any role in this problem.
            See the forum FAQ {message:id=9360002}

            Don't forget to ask your question. Does the trigger do what you want? If not, include an example in your test script. If you get an error message, post the complete error message.

            It looks like the trigger takes care of some things that happen when a boat is returned. Will you ever UPDATE the charter table and not have it indicate that a boat has been returned? For example, if you realize you entered the wrong due_date or cus_id, and so you UPDATE the table to correct that mistake before the boat has been returned: do you want the trigger to fire in that situation? Under what circumstances do you want the trigger to fire, and when do you want it not to fire?
            • 3. Re: Class Project - Help with triggers
              979118
              Frank, Thanks for the help. First I am sorry about the spacing. The original script is written in notepad and it keeps adding the spaces when copy and pasting, not really sure why.

              We are looking to have this trigger fire whenever an update is performed on charter. The trigger should update the balances in the charter table and update the availability on boat table.

              I agree about the local variables being confusing and I am not honestly happy with the way the balances are set up on the customer table either but I am working in a group and that is how it was written by another member. If it makes more sense think of curr_Bal as current charges or charges accrued from the late or early return of a boat.

              Here are the CREATE and INSERT statements for Charter, Customer, and boat, as well as the foreign keys.
              --Create Charter table
              Create Table Charter (
              Charter_ID     Number(10)  PRIMARY KEY,
              Date_Rent     Date, 
              Date_Due     Date, 
              Date_Return    Date,
              Sup_ID             Number(5),
              Equip_ID             Number(10),
              Boat_ID              Varchar2(10),
              Cus_ID          Varchar2(10),
              Disp_ID          Varchar2(10));
              
              -- Insert records into Charter table
              INSERT INTO Charter VALUES (9901,TO_DATE('02-12-2011','MM-DD-YYYY'),TO_DATE('09-12-2011','MM-DD-YYYY'),TO_DATE('09-12-2011','MM-DD-
              YYYY'), 4444, 3111, 'B001' , 'C111','D111');
              INSERT INTO Charter VALUES (9902,TO_DATE('02-12-2012','MM-DD-YYYY'),TO_DATE('09-12-2012','MM-DD-YYYY'),TO_DATE('09-12-2012','MM-DD-YYYY'),1111,3222,'B002','C222','D333');
              INSERT INTO Charter VALUES (9903,TO_DATE('02-06-2012','MM-DD-YYYY'),TO_DATE('09-06-2012','MM-DD-YYYY'),TO_DATE('09-06-2012','MM-DD-YYYY'),2222,3333,'B003','C333','D444');
              
              INSERT INTO Charter VALUES (9904,TO_DATE('12-06-2012','MM-DD-YYYY'),TO_DATE('12-15-2012','MM-DD-YYYY'),TO_DATE('12-15-2012','MM-DD-YYYY'),4444,3444,'B004','C444','D111');
              
              INSERT INTO Charter VALUES (9908,TO_DATE('12-06-2012','MM-DD-YYYY'),TO_DATE('12-15-2012','MM-DD-YYYY'),TO_DATE('','MM-DD-YYYY'),4444,3444,'B001','C111','D222');
              
              INSERT INTO Charter VALUES (9907,TO_DATE('12-04-2012','MM-DD-YYYY'),TO_DATE('12-16-2012','MM-DD-YYYY'),TO_DATE('','MM-DD-YYYY'),4444,3444,'B003','C222','D222');
              
              --Create Boat table 
              
              CREATE  TABLE Boat (
              Boat_ID           Varchar2(15) PRIMARY KEY,
              Owner_ID           Varchar2(15),
              Boat_Size           Number(10,2),
              Boat_Name            Varchar2(15),
              Boat_AVAILABILITY      Varchar2(1));
              
              --Inserting records into Boat 
              INSERT INTO Boat  VALUES('B001','301',30.50,'Di Caprio','Y');
              INSERT INTO Boat  VALUES('B002','302',44.50,'TITANIC','N');
              INSERT INTO Boat  VALUES('B003','303',40.00,'ELIZABETH-Queen','N');
              INSERT INTO Boat  VALUES('B004','304',51.25,'5 STARS','Y');
              
              --Create Customer table
              
              CREATE TABLE Customer (
              Cus_ID       Varchar2(10) PRIMARY KEY,
              Cus_FName       Varchar2(20),
              Cus_LName       Varchar2(20),
              Cus_City       Varchar2(15),
              Prev_Bal              Number(10),  
              Curr_Bal       Number(10),
              Total_Bal        Number(10));
              
              --Inserting records into Customer
              INSERT INTO  Customer VALUES ('C111','Tom','Smith','Saint Cloud',20,0,0);
              INSERT INTO  Customer VALUES ('C222','Harry','Potter','Saint Paul',50,0,0);
              INSERT INTO  Customer VALUES ('C333','Vin','Diesel','Sauk Rapids',0,150,0);
              INSERT INTO  Customer VALUES ('C444','Angelina','Julie','Minneapolis',500,0,0);
              
              ---Foreign Keys created for tables that contain foreign keys 
              Alter Table Charter
              Add Constraint Charter_Sup_ID_FK Foreign KEY (Sup_ID) REFERENCES Supplier (Sup_ID);
              Alter Table Charter
              Add Constraint Charter_Equip_ID_FK Foreign KEY (Equip_ID) REFERENCES Equipment (Equip_ID);
              Alter Table Charter
              ADD CONSTRAINT Charter_Cus_ID_FK Foreign Key (Cus_ID) REFERENCES Customer (Cus_ID);
              Alter Table Charter
              ADD CONSTRAINT Charter_Boat_ID_FK Foreign Key (Boat_ID) REFERENCES Boat (Boat_ID);
              Alter Table Charter
              Add Constraint Charter_Disp_ID_FK Foreign KEY (Disp_ID) REFERENCES Dispatcher (Disp_ID);
              Alter Table Boat
              ADD CONSTRAINT Boat_Owner_ID_FK Foreign Key (Owner_ID) REFERENCES Owner (Owner_ID);
              Alter Table Maintenance
              ADD CONSTRAINT Maintenance_Boat_ID_FK Foreign Key (Boat_ID) REFERENCES Boat (Boat_ID);
              Alter Table Maintenance
              ADD CONSTRAINT Maintenance_Owner_ID_FK Foreign Key (Owner_ID) REFERENCES Owner (Owner_ID);
              Alter Table Maintenance
              ADD CONSTRAINT Maintenance_Facility_ID_FK Foreign Key (Facility_ID) REFERENCES  Facility (Facility_ID);
              The trigger works for updating the boat availability but we are having trouble with the updating of balances.
              How could I restructure the trigger so as to avoid using the local variables because when I tried changing the day operation I get an error stating the OLD.date_due isnt declared.
              -- TRIGGER 2 to charge &75 late fee or refund $20 for early return 
              Create or replace Trigger Rental_Fee_AU
              After Update on Charter
              For each row
              Declare
              Amount Number(4);
              Day    Number(4);
              Begin
              Day := :New.Date_Return - Old.Date_Due;
              If Day>0 then
              Amount :=Day*75;
              Elsif Day < 0 then
              Amount :=Day*20;
              Else
              Amount :=Amount;
              End if;
              Also I am not sure about the Amount :=Amount; either. Could i simply leave the last else empty instead?

              I hope that make more sense. The help is greatly appreciated. Let me know if I can clarify my question better or the details of the project.
              • 4. Re: Class Project - Help with triggers
                jeneesh
                Some suggestions
                Create or replace Trigger Rental_Fee_AU
                after update on charter
                for each row
                --"Use when clause to make sure the trigger 
                --"will execute only when date_return is updated.
                --"Otherwise, trigger will get executed for all updates
                when (new.date_return is not null and old.date_return is null)
                declare
                    --"Use standard variable names to not to mix up with column names
                    ln_amount number(4) := 0; --"Initialize amount as zero
                    ln_day_count    number(4);
                    --"The below variables are not required
                    --"you can use column names directly
                    --ld_duedate date;
                    --ld_returndate date;
                begin
                    --ld_duedate:=:old.date_due;
                    --ld_returndate:=:new.date_return;
                    --"you can use column names directly in the below calculation
                    ln_day_count :=:new.date_return-:old.date_due;
                    if ln_Day_count > 0 then
                        ln_Amount := ln_Day_count*75;
                    elsif ln_Day_count < 0 then
                        ln_amount := ln_day_count*20;
                    --else --"Not required
                   --     ln_Amount :=Amount;
                    End if;
                    --"do all the updates together
                    update customer
                    set curr_bal = curr_bal+ln_amount,
                        Total_Bal = curr_bal+ln_amount+Prev_bal
                    Where Cus_ID=:Old.Cus_ID;
                    --"below update is commented as it is already done
                    /*Update Customer
                    Set Total_Bal = Curr_Bal + Prev_bal
                    Where :Old.Cus_ID=Cus_ID;*/
                    --Update Availability
                    Update Boat
                    Set Boat_AVAILABILITY = 'Y'
                    where boat.boat_id=:new.boat_id;
                End;
                {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                • 5. Re: Class Project - Help with triggers
                  Frank Kulash
                  Hi,
                  976115 wrote:
                  ... The trigger works for updating the boat availability but we are having trouble with the updating of balances.
                  Give an example or two, such as "When the tables contain the data posted above, and I issue this statement: UPDATE charter ... then the trigger changes the customer table to ... but I want it to be ... because ..."
                  How could I restructure the trigger so as to avoid using the local variables because when I tried changing the day operation I get an error stating the OLD.date_due isnt declared.
                  In the body of the trigger, :OLD and :NEW always need the colons.
                  -- TRIGGER 2 to charge &75 late fee or refund $20 for early return 
                  Create or replace Trigger Rental_Fee_AU
                  After Update on Charter
                  For each row
                  Declare
                  Amount Number(4);
                  Day    Number(4);
                  Begin
                  Day := :New.Date_Return - Old.Date_Due;
                  So you need a colon before OLD. Also, I would use more descriptive variable names
                  Days_Late := :NEW.Date_Return - :OLD.Date_Due;
                  Why are you using :OLD.Due_Date, not :NEW.Due_Date? If someone changes the Due_Date at the time the boat is returned, don't you want to use the corrected Due_Date?
                  If Day>0 then
                  Amount :=Day*75;
                  Elsif Day < 0 then
                  Amount :=Day*20;
                  Else
                  Amount :=Amount;
                  End if;
                  Also I am not sure about the Amount :=Amount; either. Could i simply leave the last else empty instead?
                  It's more conventional to omit the ELSE branch altogether:
                  IF  Days_Late > 0
                  THEN
                      Late_Charge := Days_Late * 75;
                  ELSIF  Days_Late < 0
                  THEN
                      Late_Charge := Days_Late * 20;
                  END IF;
                  But, even if Days_Late = 0, you always want Late_Charge to have some value, perhaps 0, don't you?
                  Maybe you want to set that value to 0 when you declare the variable:
                  Late_Charge  NUMBER  := 0;
                  I hope that make more sense. The help is greatly appreciated. Let me know if I can clarify my question better or the details of the project.
                  • 6. Re: Class Project - Help with triggers
                    stratmo
                    Hi,

                    thanks for providing your script. I commented statements regarding the table SUPPLIER, EQUIPMENT, MAINTENANCE, FACILITY, DISPATCHER (probably others) out because I couldn't find the DDL for them. Those are referenced in your foreign-key definitions.
                    If this is just an excercise then it is probably not a very good one. Please read under the following link [url http://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html] The trouble with Triggers  what Tom Kyte thinks about triggers.
                    Lets pretend the three shown tables are there and filled.
                    I added the following foreign keys
                    Alter Table Charter
                    ADD CONSTRAINT Charter_Cus_ID_FK Foreign Key (Cus_ID) REFERENCES Customer (Cus_ID);
                    Alter Table Charter
                    ADD CONSTRAINT Charter_Boat_ID_FK Foreign Key (Boat_ID) REFERENCES Boat (Boat_ID);
                    and left out the others because of the missing tables mentioned above.

                    The logic you implemented should not be implemented in a trigger. Following the thoughts in the mentioned article.
                    What does another developer than you or you yourself in five years keep away from creating a new trigger on BOAT which fires against charter.
                    So triggers shoot around your schema nobody knowing what's happening. Probably without anybody detecting it for a long period of time damaging your data-consistency.
                    Your trigger seems to realize a kind of business-rule.
                    I know one should not opt for PL/SQL first but in this case I think the whole UPDATE with the dependent processes should be realized with a procedure.
                    So the whole process could be treated as an atomic step with some according error handling and a COMMIT (?) at the end.

                    Following the thoughts of JANEESH and all the others in this post above.
                    I just took the trigger-logic and put it into a procedure with the rowtype of the charter-table as input-parameter. Hopefully this gets near your requirements.
                    -- to show there is nothing "up my sleeves"
                    drop trigger Rental_Fee_AU;
                    drop procedure Rental_Fee_Au;
                    create or replace
                    Procedure Rental_Fee_Au (pir_charter in charter%rowtype)
                    as
                    
                    -- stratmo: the following code is taken from the post by JANEESH!!!
                    -- with the needed adjustments
                    
                        --"Use standard variable names to not to mix up with column names
                        ln_amount        number(4) := 0; --"Initialize amount as zero
                        ln_day_count     number(4);
                        
                        -- stratmo: not required, used here for readability
                        Lr_Old_Charter_Row     Charter%Rowtype;
                        Lr_New_Charter_Row     Charter%Rowtype   := pir_charter;
                       
                        
                        --"The below variables are not required
                        --"you can use column names directly
                        --ld_duedate date;
                        --ld_returndate date;
                    begin
                       -- stratmo: get the old values for the corresponding row in the charter-table
                       begin -- get old charter-values
                          select Charter_Id, Date_Rent, Date_Due, Date_Return, Sup_Id, Equip_Id, Boat_Id, Cus_Id, Disp_ID
                          Into Lr_Old_Charter_Row
                          from Charter
                          where  charter_id = Lr_New_Charter_Row.charter_id;
                       exception when no_data_found
                       Then
                          Raise_Application_Error(-20101,  'Charter with Charter_id ' 
                                                           || Lr_Old_Charter_Row.Charter_Id 
                                                           ||'not initialized');
                       end ; -- END get old charter-values
                       -- stratmo: you had an after update trigger first, so i placed the Update at the start
                       -- WHAT shall happen if there is no matching charter see exception-handler above?
                       -- Shall all columns be overwritten?
                       Update Charter
                       set  Date_Rent = Lr_New_Charter_Row.date_rent
                          , Date_Due = Lr_New_Charter_Row.date_due
                          , Date_Return = Lr_New_Charter_Row.date_return
                          , Sup_Id = Lr_New_Charter_Row.sup_id
                          , Equip_Id = Lr_New_Charter_Row.equip_id
                          , Boat_Id = Lr_New_Charter_Row.boat_id
                          , Cus_Id = Lr_New_Charter_Row.Cus_id
                          , Disp_ID = Lr_New_Charter_Row.Disp_id
                       where charter_id = Lr_New_Charter_Row.charter_id;
                    
                    
                        --ld_duedate:=:old.date_due;
                        --ld_returndate:=:new.date_return;
                        --"you can use column names directly in the below calculation
                        
                        -- stratmo: what happens if date_return < date_due; 
                        -- obviously this is caused by a wrong entry but it should be handled
                        ln_day_count := lr_new_charter_row.date_return - lr_old_charter_row.date_due;
                    
                        if ln_Day_count > 0 then
                            ln_Amount := ln_Day_count*75;
                        elsif ln_Day_count < 0 then
                            ln_amount := ln_day_count*20;
                        --else --"Not required
                       --     ln_Amount :=Amount;
                        End if;
                        --"do all the updates together
                        update customer
                        set curr_bal = curr_bal+ln_amount,
                            Total_Bal = curr_bal+ln_amount+Prev_bal
                        Where Cus_ID=lr_old_charter_row.Cus_ID;
                        --"below update is commented as it is already done
                        /*Update Customer
                        Set Total_Bal = Curr_Bal + Prev_bal
                        Where :Old.Cus_ID=Cus_ID;*/
                        --Update Availability
                        Update Boat
                        Set Boat_AVAILABILITY = 'Y'
                        where boat.boat_id=lr_old_charter_row.boat_id;
                        
                        -- stratmo: Do you want to commit here?
                        -- if the above section is an atomic buisiness logic, you may want to commit
                        -- right here; if not you may use a additional parameter to define the behaviour
                        -- regarding a commit
                        
                    End;
                    /
                    Here is an anonymous block which I used for a very(!) short test!
                    drop table charter_save;
                     create table charter_save as 
                     select Charter_Id charter_id, Date_Rent date_rent, Date_Due, Date_Return, Sup_Id, Equip_Id, Boat_Id, Cus_Id, Disp_ID
                     from charter
                     where  charter_id = 9901;
                     
                    -- truncate table charter_save;
                    
                    -- example for a call of the procedure
                    declare
                       lr_charter_test_row  charter%rowtype;
                    begin
                       select Charter_Id charter_id, Date_Rent date_rent, Date_Due, Date_Return, Sup_Id, Equip_Id, Boat_Id, Cus_Id, Disp_ID
                       into lr_charter_test_row
                       from charter
                       where  charter_id = 9901; 
                       
                       lr_charter_test_row.date_rent := to_date('20110929','YYYYMMDD');
                       lr_charter_test_row.date_due := to_date('20111008','YYYYMMDD');
                       lr_charter_test_row.date_return := to_date('20111025','YYYYMMDD');
                       
                       Rental_Fee_Au (pir_charter => lr_charter_test_row);
                    end;
                    /
                    
                    select * from (
                       select 'charter' tabname, z.* from charter z
                       union all
                       select 'charter_save' tabname, y.* from charter_save y
                    ) h
                    where h.charter_id = 9901;
                    Bye

                    stratmo
                    • 7. Re: Class Project - Help with triggers
                      Stew Ashton
                      How are you expecting to update a table in an "AFTER UPDATE" trigger?

                      To quote Tom Kyte:
                      use BEFORE FOR EACH row when you need to WRITE to the :new record.
                      use AFTER FOR EACH row triggers when you want to VALIDATE the final values
                      in the :new record
                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:29259877603106

                      P.S. I see now, you are actually updating the row again after the original update is complete. It would be more common to use a BEFORE trigger and simply change the :NEW values to what you want -- those changes would then be part of the original update.

                      In the real world I would never do this kind of processing in a trigger.

                      Edited by: Stew Ashton on Dec 11, 2012 5:16 PM