Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2012
    Posts
    24

    Unanswered: HW again. Help please

    Hello again. I have a new problem. My professor has not explain triggers and only gave an example to do one for an auto increment primary key. so im lost as to try an make this work. Can anyone point me to the right direction? The sequence has been declared already. Though it could be wrong as I am just that lost after 3 straight days of trying to get this to work.
    Thanks for any help
    Code:
    CREATE OR REPLACE TRIGGER airportBeforeIURow
    BEFORE INSERT OR UPDATE ON jpowell12.airport
    FOR EACH ROW
    
    BEGIN
    SELECT airport_squence.NEXTVAL, destination, name, city, state, landing_fee, tie_down_fee, restrictions
    INTO :new.destination
    FROM DUAL;
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('ARM','Aeromexpress','College Park','GA',100,55,NULL); 
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('ATA','Athens','Athens','GA',95,65.5,'Single engine only:after 10:00am before 4:00pm  after 7:00 pm');
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('ATL','Hartsfield Atlanta','Atlanta','GA',775,75,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('BDC','Baldwin County','Millegeville','GA',98,75,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('BNA','Metropolitan Nashville','Nashville','TN',350,65.95,'Single engine;12 seat max;between 10:00 am - 3:30 pm');
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('CAM','C A Moore Municipal','Lexington','MS',150,78.9,'multi engine;20 seater max;anytime');
    
    insert INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('CMA','Charleston Municipal','Charleston','MS',200,50,'any type of plane;any time - must have 24 hours notice;tiedow');
    
    insert INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('GMA','Greenville Municipal','Greenville','MS',175,56.85,'single engine only:between 11:30am - 2:30pm;48hr notice;check');
    
    insert INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('GNV','Gulfport-Biloxi','Gulf Port','MS',200,50,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('GVM','G V Montgomery','Forest','MS',90,50,'Limited to 24 seaters;24 hr notice');
    
    insert INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('MOB','Melbourne International','Melbourne','FL',125,75, NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('MQY','Coastal Planes','Mount Olive','NC',105,89.9,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('RLA','Reelfoot Lake','Tiptonville','TN',205,90.9,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('SCR','Summer County Regional','Gallatin','TN',345,135.9,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('SFA','Scott Field','Parsons','TN',125,75,'Single engine only;24 hr notice');
    
    insert into AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('SRC','Smyrna/Rutherford County','Smyrna','TN',210,56.78,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('STL','Huntsville International','Huntsville','AL', 175,59.8,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('TYS','Mc Gee Tyson','Knoxville','TN',203,95.9,NULL);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('ABQ','Albuquerque International','Albuquerque','NM',230,75,'All aircraft; 12 hr notice');
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('PWM','PORTLAND INTL JETPORT', 'PORTLAND', 'ME',250.00, 150.00, null);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('TTN', 'Trenton Mercer AIRPORT','TRENTON', 'NJ', 150.00, 75.00, null);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('VJI','VIRGINIA HIGHLANDS', 'ABINGDON','VA', 275.00, 150.00, 'SINGLE ENGINE ONLY');
    
    COMMIT;
    
    END airportBeforeIURow;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have a new problem.
    What exactly is your problem?

    What are the requirements that the trigger needs to satisfy?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by anacedent View Post
    >I have a new problem.
    What exactly is your problem?

    What are the requirements that the trigger needs to satisfy?
    Thats just it, idk. My teacher has left me blind and wont answer me. all the top inserts work right cept these lat three.
    HTML Code:
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('PWM','PORTLAND INTL JETPORT', 'PORTLAND', 'ME',250.00, 150.00, null);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('TTN', 'Trenton Mercer AIRPORT','TRENTON', 'NJ', 150.00, 75.00, null);
    
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('VJI','VIRGINIA HIGHLANDS', 'ABINGDON','VA', 275.00, 150.00, 'SINGLE ENGINE ONLY');
    Very few constraints. Just capitals on first char

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Gosh! What a mess! A trigger is supposed to create a primary key column value ONLY, not to do everything you put into it.

    I don't think that you should blame your teacher, but yourself. Oracle documentation can be accessed (and/or downloaded) for free (here's the Oracle 10g link), so - if you didn't really understand what he/she said about triggers, YOU should have searched for it IN THE DOCUMENTATION, study what's written in there and apply it to your homework.

    As of code you composed:

    How did you plan to select "airport_squence.NEXTVAL, destination, name, city, state, landing_fee, tie_down_fee, restrictions" into a single "destination" (i.e. 8 values into 1 variable)?

    You don't COMMIT in a trigger. You can't. (OK, you can, if you make it an autonomous transaction, but pretend you never saw me saying this).

    Here's an example for what you should have done:
    Code:
    SQL> create table airport
      2    (dest_id   number,
      3     dest_name varchar2(20)
      4    );
    
    Table created.
    
    SQL> create sequence seq_air;
    
    Sequence created.
    
    SQL> create or replace trigger trg_bi_air
      2    before insert on airport
      3    for each row
      4  begin
      5    select seq_air.nextval into :new.dest_id from dual;
      6  end;
      7  /
    
    Trigger created.
    
    SQL> insert all
      2    into airport (dest_name) values ('London')
      3    into airport (dest_name) values ('Paris')
      4  select * from dual;
    
    2 rows created.
    
    SQL> select * from airport;
    
       DEST_ID DEST_NAME
    ---------- --------------------
             1 London
             2 Paris
    
    SQL>

  5. #5
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by Littlefoot View Post
    Gosh! What a mess! A trigger is supposed to create a primary key column value ONLY, not to do everything you put into it.

    I don't think that you should blame your teacher, but yourself. Oracle documentation can be accessed (and/or downloaded) for free (here's the Oracle 10g link), so - if you didn't really understand what he/she said about triggers, YOU should have searched for it IN THE DOCUMENTATION, study what's written in there and apply it to your homework.

    As of code you composed:

    How did you plan to select "airport_squence.NEXTVAL, destination, name, city, state, landing_fee, tie_down_fee, restrictions" into a single "destination" (i.e. 8 values into 1 variable)?

    You don't COMMIT in a trigger. You can't. (OK, you can, if you make it an autonomous transaction, but pretend you never saw me saying this).

    Here's an example for what you should have done:
    Code:
    SQL> create table airport
      2    (dest_id   number,
      3     dest_name varchar2(20)
      4    );
    
    Table created.
    
    SQL> create sequence seq_air;
    
    Sequence created.
    
    SQL> create or replace trigger trg_bi_air
      2    before insert on airport
      3    for each row
      4  begin
      5    select seq_air.nextval into :new.dest_id from dual;
      6  end;
      7  /
    
    Trigger created.
    
    SQL> insert all
      2    into airport (dest_name) values ('London')
      3    into airport (dest_name) values ('Paris')
      4  select * from dual;
    
    2 rows created.
    
    SQL> select * from airport;
    
       DEST_ID DEST_NAME
    ---------- --------------------
             1 London
             2 Paris
    
    SQL>
    Wow, thank you for the response. Everything i posted with the INSERT INTO came from my teacher. So if a trigger is only suppose to do primary key, then why is she misleading us? this is 11g and the book has nothing at all about triggers, even for $85 lol. But i have been looking all over on the internet for examples and others problems that have been fixed

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    A trigger is not only used to assign primary keys, just the example you are referencing. A trigger is a very powerful mechanism. Try the following google search string

    oracle trigger examples
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Oct 2012
    Posts
    24
    Ok so far this is what I have come up with.
    Code:
    CREATE OR REPLACE TRIGGER airportBeforeIURow
    BEFORE INSERT OR UPDATE ON jpowell12.airport
    FOR EACH ROW
    
    BEGIN
    IF INSERTING THEN
    SELECT airport_sequence.NEXTVAL
    INTO :new.destination
    FROM DUAL;
    ELSE UPDATE THEN
    SELECT airport_sequence.NEXTVAL
    INTO :old.destination
    FROM DUAL;
    END IF;
    
    :new.destination :=upper(:new.destination);
    :new.name :=initcap(:new.name);
    :new.city :=initcap(:new.city);
    :new.state :=upper(:new.state);
    :old.destination :=upper(:old.destination);
    :old.name :=initcap(:old.name);
    :old.city :=initcap(:old.city);
    :old.state :=upper(:old.state);
    end;
    /
    This kicks back these errors after adding the ld and ELSE UPDATE parts
    Code:
    Error(6,6): PL/SQL: SQL Statement ignored
    Error(6,13): PL/SQL: ORA-00903: invalid table name
    I also have to clean up the data that got re-entered repeatedly from prior attempts with the INSERT part. Though I noticed that it changed the DESTINATION values to auto increment digits instead of the actual value to be inserted. Any ideas why that is happening?

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    a id column should not change during update. You can not modify the old value, only the new. and uppercasing a number makes no sense. A sequence is always a number.This is how I would do it.
    Code:
    CREATE OR REPLACE TRIGGER airportBeforeIURow
    BEFORE INSERT OR UPDATE ON jpowell12.airport
    FOR EACH ROW
    BEGIN
    IF INSERTING THEN
    SELECT airport_sequence.NEXTVAL
    INTO :new.destination
    FROM DUAL;
    ELSE UPDATING THEN
    :NEW.DESTINATION := :old.destination;
    END IF;
    
    :new.name :=initcap(:new.name);
    :new.city :=initcap(:new.city);
    :new.state :=upper(:new.state);
    end;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Oct 2012
    Posts
    24
    Quote Originally Posted by beilstwh View Post
    a id column should not change during update. You can not modify the old value, only the new. and uppercasing a number makes no sense. A sequence is always a number.This is how I would do it.
    Code:
    CREATE OR REPLACE TRIGGER airportBeforeIURow
    BEFORE INSERT OR UPDATE ON jpowell12.airport
    FOR EACH ROW
    BEGIN
    IF INSERTING THEN
    SELECT airport_sequence.NEXTVAL
    INTO :new.destination
    FROM DUAL;
    ELSE UPDATING THEN
    :NEW.DESTINATION := :old.destination;
    END IF;
    
    :new.name :=initcap(:new.name);
    :new.city :=initcap(:new.city);
    :new.state :=upper(:new.state);
    end;
    /
    The thing is, DESTINATION is not a number. But it is the PK. Here is what she wants me to insert.
    Code:
    INSERT INTO AIRPORT
    (destination, name, city, state, landing_fee, tie_down_fee, restrictions)
    VALUES('ARM','Aeromexpress','College Park','GA',100,55,NULL);
    A whole bunch like this. That is why I need to UPPER the DESTINATION

  10. #10
    Join Date
    Oct 2012
    Posts
    24
    Ok sequence is causing it and it is not needed for my trigger. Time to try again lol

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •