Created trigger in Oracle PL/SQL, doesn;t seem to do anything

Question

I am trying to learn how to implement triggers using PL/SQL. So I have a sample database that I've put together, and I have a trigger that to my very inexperienced eye looks like it should work:

CREATE OR REPLACE TRIGGER updateBeerCount
AFTER INSERT ON beerTable
FOR EACH ROW
DECLARE 
pragma autonomous_transaction;
beers_produced INTEGER;
BEGIN
    SELECT COUNT(beer_name) INTO beers_produced FROM beerTable WHERE brewery = :NEW.brewery;
    UPDATE breweryTable SET number_of_beers_produced = beers_produced WHERE brewery = :NEW.brewery;
    COMMIT;
END;
/

Basically there are two tables here that are important - beerTable, which contains a list of beers with their breweries, and breweryTable, which contains a list of breweries and a count of how many different beers they are producing. So the number_of_beers_produced should be the total number of rows in the beerTable where that brewery is listed (brewery being a foreign key in beerTable

As it is the code runs and isn't giving any errors in the command line where I'm running it, but the trigger doesn't actually do anything either - the number_of_beers_produced is zero for every brewery regardless of how many of their beers are in the database.

Is there something really obviously wrong with what I'm doing here, or is there any obvious way of debugging this? I'm used to IDEs where I can step through code and see what's happening, I assume there's no way of doing that here?

EDIT - MORE DETAILS:

Tables are created as follows:

create table beerStyleTable (
beer_style varchar(20) primary key,
country_of_origin varchar(13),
typical_IBU number,
typical_colour_EBC number,
typical_ABV number
);

create table breweryTable (
brewery varchar(20) primary key,
country varchar(13),
output_in_HL number,
year_founded number,
number_of_beers_produced number
);

create table beerTable (
beer_name varchar(24),
beer_style varchar(20),
brewery varchar(20),
IBU number,
Colour number,
ABV number,
PRIMARY KEY (beer_name, brewery),
FOREIGN KEY (beer_style) REFERENCES beerStyleTable(beer_style),
FOREIGN KEY (brewery) REFERENCES breweryTable(brewery)
);

Then populated like this:

insert into breweryTable values('Porterhouse', 'Ireland', 10000, 1989, 0);

insert into beerStyleTable values ('Irish Stout', 'Ireland', 35, 80, 4.5);

insert into beerTable values ('Oyster Stout', 'Irish Stout', 'Porterhouse', 45, 75, 5.2);

Then the query

SELECT * FROM breweryTable;

gives the output

Porterhouse Ireland 10000 1989 0

I wondered about giving the initial value of 0 for number_of_beers_produced, but giving it as NULL doesn't work either (it just means that I don't get any number out for that field for the above select query).

Thanks for any help you can offer!

Diarmuid

Answer 1

Your trigger doesn't update breweryTable because you're using an autonomous transaction.

When you insert a beer into beerTable, only the session that inserted that beer can see that beer, until you COMMIT. When inserting, updating or deleting, your session opens a transaction, which is closed when you COMMIT or ROLLBACK. The trigger is using an autonomous transaction, which is separate from the transaction that your session has. Neither transaction can see uncommitted data modified by the other.

Here's an example run with three beers I've made up. I have two breweries, imaginatively called brewery 1 and brewery 2:

SQL> insert into beerTable (beer_name, brewery) values ('Eeuurgh', 'brewery 1');

1 row created.

SQL> insert into beerTable (beer_name, brewery) values ('Nasty Nasty', 'brewery 2');

1 row created.

SQL> insert into beerTable (beer_name, brewery) values ('Who drinks this stuff?', 'brewery 2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from breweryTable;

BREWERY                        NUMBER_OF_BEERS_PRODUCED
------------------------------ ------------------------
brewery 1                                             0
brewery 2                                             0

The trigger fired three times, once for each INSERT statement, but because it ran in an autonomous transaction, it couldn't see the beers being entered so it reported counts of zero.

On the other hand, if we commit after each row, we get the following result:

SQL> truncate table beerTable;

Table truncated.

SQL> insert into beerTable (beer_name, brewery) values ('Eeuurgh', 'brewery 1');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into beerTable (beer_name, brewery) values ('Nasty Nasty', 'brewery 2');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into beerTable (beer_name, brewery) values ('Who drinks this stuff?', 'brewery 2');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from breweryTable;

BREWERY                        NUMBER_OF_BEERS_PRODUCED
------------------------------ ------------------------
brewery 1                                             0
brewery 2                                             1

In this case, when the trigger fired for the third time, it was able to see the beer Nasty Nasty, brewed by brewery 2, since by that time that beer had been committed. However, it couldn't see the beer Who drinks this stuff? because that beer hadn't yet been committed. Hence the count for brewery 2 is 1.

As you noted, removing the autonomous transaction didn't work: you got an ORA-04091 'table is mutating' error instead.

I have to ask why you want to keep these counts updated like this. Will you be using them often enough to make it worth keeping them up-to-date as you go?

EDIT: I can't think of a situation in which keeping these counts up-to-date would be useful. There's a fair bit of work involved with keeping them up-to-date (you've got to write triggers to handle INSERTs, UPDATEs and DELETEs), and there may also be performance problems if you run an UPDATE or a DELETE that affects many rows.

An alternative approach that might avoid the mutating-table errors would be to increment or decrement the counts for the affected brewery/ies in the trigger. However, it's possible for these counts to get out of sync with what's actually in the table, and I'm not convinced that it would completely fix the performance issues either.

Keeping track of these counts in this way is ultimately something that's more trouble than it's worth.