Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Your trigger doesn't update <code>breweryTable</code> because you're using an autonomous transaction.</p> <p>When you insert a beer into <code>beerTable</code>, only the session that inserted that beer can see that beer, until you <code>COMMIT</code>. When inserting, updating or deleting, your session opens a transaction, which is closed when you <code>COMMIT</code> or <code>ROLLBACK</code>. 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.</p> <p>Here's an example run with three beers I've made up. I have two breweries, imaginatively called <code>brewery 1</code> and <code>brewery 2</code>:</p> <pre> 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 </pre> <p>The trigger fired three times, once for each <code>INSERT</code> statement, but because it ran in an autonomous transaction, it couldn't see the beers being entered so it reported counts of zero.</p> <p>On the other hand, if we commit after each row, we get the following result:</p> <pre> 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 </pre> <p>In this case, when the trigger fired for the third time, it was able to see the beer <code>Nasty Nasty</code>, brewed by <code>brewery 2</code>, since by that time that beer had been committed. However, it couldn't see the beer <code>Who drinks this stuff?</code> because that beer hadn't yet been committed. Hence the count for <code>brewery 2</code> is 1.</p> <p>As you noted, removing the autonomous transaction didn't work: you got an ORA-04091 'table is mutating' error instead.</p> <p>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?</p> <p><strong>EDIT</strong>: 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. </p> <p>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.</p> <p>Keeping track of these counts in this way is ultimately something that's more trouble than it's worth.</p>
    singulars
    1. This table or related slice is empty.
    1. POCreated trigger in Oracle PL/SQL, doesn;t seem to do anything
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USLuke Woodward
    1. USLuke Woodward
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAh, that makes a lot of sense, thanks! As for your question - it's not so much that I'll be using these counts (as I mentioned, this is just an exercise, so this database won't ever be "used", as such), rather that since it doesn't work I want to know why, and how I might get around the problem. In this particular example the count is probably superfluous, sure, but I'm sure there are other examples where you might want running counts without having to go and manually call count each time. Is this doable, or is it one of those things that's way more trouble than it's worth? Thanks.
      singulars
      1. PO
      1. USDiarmuid Ó Muirgheasa
    2. CO@DiarmuidÓMuirgheasa: see my updated answer.
      singulars
      1. PO
      1. USLuke Woodward
    3. COThanks for that - the detailed answer is much appreciated!
      singulars
      1. PO
      1. USDiarmuid Ó Muirgheasa
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload