CODEWITHSUNDEEP
sqloracle-databasetriggers
VGe0rge
VGe0rge

Reputation: 1090

Oracle PL/SQL Trigger to update another column

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)

Upvotes: 0

Views: 12343

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

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.

Upvotes: 3

Big Ed
Big Ed

Reputation: 1244

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.

Upvotes: 1

Related Questions