0

I am trying to create a trigger that updates another table with PL/SQL and I am having some problems. (I have read this but doesn't help a lot).

Here is my situation, I have lets say 2 tables :

Customers Table

CustomerID number Primary Key, ItemsDelivered number

Items Table

CustomerID number, ItemID number, ItemDelivered Varchar(15)

Lets say that when somenone places an order we have a new record at Items table that looks like this: 

| CustomerID | ItemID | ItemDelivered | 
|         1  |    1   |    False      |

I want a trigger that will raise the ItemsDelivered counter whenever someone updates the ItemDelivered collumn to "True".

create or replace Trigger UpdateDelivered  
   After Update On Items For
     Each Row 
Declare  
   Counter Customers.ItemsDelivered%Type; 
Begin
  If (:Old.ItemDelivered ='False' And :New.ItemDelivered='True') Then
     Select ItemsDelivered into Counter From Customers where CustomerdID =:New.CustomerID; 
     Update....
  end if; 
END;

Here is my problem, if only the ItemDelivered column is updated there is no New.CustomerID!

Is there any way to get the CustomerID of the row that have just updated? (I have tried to join with inserted virtual table but I am getting an error that the table doesn't exists)

Improve this question
3
  • 2
    Old.CustomerID is what you are looking for, I guess. But do you really want to do this? Just join and count when you need the counts. Eventually you are going to start having integrity issues by trying to maintain calculated columns like "ItemsDelivered". Commented Apr 6, 2012 at 15:46
  • 1
    @VGeorge: please elaborate what you want to do ,why you want :New.customerID ? and i thnk you want the :OLD.customerID ? Commented Apr 6, 2012 at 15:47
  • Thanks really much for your help! The old parameter worked like a charm!! My situation is a little different from that i have described, i simplified it a little. The thing i want to do actually is to raise the salary of a person whenever go from "Not married" to "Married" .Thanks really much,i really appreciate the help:) Commented Apr 6, 2012 at 16:01

2 Answers 2

Reset to default
3

In a row-level trigger on an UPDATE, both :new.customerID and :old.customerID should be defined. And unless you're updating the CustomerID, the two will have the same value. Given that, it sounds like you want

create or replace Trigger UpdateDelivered  
   After Update On Items For
     Each Row 
Begin
  If (:Old.ItemDelivered ='False' And :New.ItemDelivered='True') Then
     Update Customers
        set itemsDelivered = itemsDelivered + 1
      where customerID = :new.customerID;
  end if; 
END;

That being said, however, storing this sort of counter and maintaining it with a trigger is generally a problematic way to design a data model. It violates basic normalization and it potentially leads to all sorts of race conditions. For example, if you code the trigger the way you were showing initially where you do a SELECT to get the original count and then do an update, you'll introduce bugs in a multi-user environment because someone else could also be in the process of marking an item delivered and neither transaction would see the other session's changes and your counter would get set to the wrong value. And even if you implement bug-free code, you've got to introduce a serialization mechanism (in this case the row-level lock on the CUSTOMERS table taken out by the UPDATE) that causes different sessions to have to wait on each other-- that is going to limit the scalability and performance of your application.

To demonstrate that the :old.customerID and the :new.customerID will both be defined and will both be equal

SQL> desc items
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMERID                                         NUMBER
 ITEMID                                             NUMBER
 ITEMDELIVERED                                      VARCHAR2(10)


SQL> ed
Wrote file afiedt.buf

  1  create or replace
  2  trigger updateDelivered
  3    after update on items
  4    for each row
  5  begin
  6    if( :old.itemDelivered = 'False' and :new.itemDelivered = 'True' )
  7    then
  8      dbms_output.put_line( 'New CustoerID = ' || :new.customerID );
  9      dbms_output.put_line( 'Old CustomerID = ' || :old.customerID );
 10    end if;
 11* end;
SQL> /

Trigger created.

SQL> select * from items;

CUSTOMERID     ITEMID ITEMDELIVE
---------- ---------- ----------
         1          1 False

SQL> update items
  2     set itemDelivered = 'True'
  3   where customerID = 1;
New CustoerID = 1
Old CustomerID = 1

1 row updated.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks really much for your time! I thought that old and new will have the same value but they haven't! When i update only the ItemDelivered column the new.customerID has no value!I have tested that, and didn't work! I agree with you about the counter thing, i will not use this for a counter,this was a simplified example. Have a good time:)
@VGe0rge - I updated my answer with a demonstration that the :old.customerID and :new.customerID will both be defined and will both be equal when you're updating the itemDelivered column.
1

If you would like to store the item count in the database, I would recommend a pair of triggers. You would use an after row trigger to record the item number (perhaps in a table variable in your package) and an after statement trigger that will actually update the counter, calculating the items delivered directly from the base date. That is, by 

select sum(itemsDelivered) from Customers where itemId = :itemId;

This way, you avoid the dangers of corrupting the counters, because you are always setting it to what it should be. It's probably a Good Idea to keep the derived data in a separate table.

We built our old system entirely on database triggers which updated data in separate "Derived" tables, and it worked very well. It had the advantage that all of our data manipulation could be performed by inserting, updating and deleting the database tables with no need to know the business rules. For instance, to put a student into a class, you just had to insert a row into the registration table; after your select statement, tuition, fees, financial aid, and everything else were already calculated.

Improve this answer

3 Comments

This is a much better solution , i must say!At that time i don't want a counter actually but i will try it whenever i want one. Thanks for your time:)
Note that you have the same sort of race conditions with this sort of approach. If there are two sessions that are both marking the same item delivered to two separate customers at roughly the same time, the two transactions will not see each other and you can easily end up storing summary information that differs from the detail. You could potentially use a fast refreshable materialized view that refreshes on commit to store summary information that will always be synchronized with the detail data.
Actually, no. The key is that you always calculate the derived values from scratch, never from their current values. That way they're always consistent with the base data. Keeping the derived data in separate tables that are never human-updated also minimizes locking issues.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.