Databases
StackOverflow2013
Postsdbo
POCreated trigger in Oracle PL/SQL, doesn;t seem to do anything
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCreated trigger in Oracle PL/SQL, doesn;t seem to do anything
    primarykey
    data
    text
    <p>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:</p> <pre><code>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; / </code></pre> <p>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 <code>number_of_beers_produced</code> should be the total number of rows in the beerTable where that brewery is listed (brewery being a foreign key in <code>beerTable</code></p> <p>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 <code>number_of_beers_produced</code> is zero for every brewery regardless of how many of their beers are in the database. </p> <p>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?</p> <p>EDIT - MORE DETAILS:</p> <p>Tables are created as follows:</p> <pre><code>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) ); </code></pre> <p>Then populated like this:</p> <pre><code>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); </code></pre> <p>Then the query</p> <pre><code>SELECT * FROM breweryTable; </code></pre> <p>gives the output</p> <pre><code>Porterhouse Ireland 10000 1989 0 </code></pre> <p>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).</p> <p>Thanks for any help you can offer!</p> <p>Diarmuid</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDiarmuid Ó Muirgheasa
    1. USDiarmuid Ó Muirgheasa
    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. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCreated trigger in Oracle PL/SQL, doesn;t seem to do anything
      1. This table or related slice is empty.
      1. VTUpMod
    1. COis there a reason you're using an autonomous transaction? Are you sure your trigger runa(ie are you inserting a new row into the table?) You should edit & add your sample input & outputs to the question
      singulars
      1. POCreated trigger in Oracle PL/SQL, doesn;t seem to do anything
      1. USSathya
    2. COI'm using the autonomous transaction because I was getting a "table is mutating" error without it, and this was the suggested fix. With it there's no error, but also it doesn't actually work. Yes, I'm inserting a new row - I'll update the question now to show a sample input. Thanks.
      singulars
      1. POCreated trigger in Oracle PL/SQL, doesn;t seem to do anything
      1. USDiarmuid Ó Muirgheasa
    3. COI had a hunch that the autonomous transaction was to cover up the table is mutating error, just wanted to confirm that.
      singulars
      1. POCreated trigger in Oracle PL/SQL, doesn;t seem to do anything
      1. USSathya
 

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