Results 1 to 4 of 4

Thread: Trigger help

  1. #1
    Join Date
    Feb 2006
    Posts
    17

    Unanswered: Trigger help

    Hi all,

    i would like to create a trigger like below:

    CREATE OR REPLACE TRIGGER accom_insert
    AFTER INSERT ON FLAT
    FOR EACH ROW
    BEGIN
    INSERT INTO ACCOMODATION (accomNo,address_type(),noOfRooms) VALUES (accomNo,address_type(),noOfRooms);
    END;

    but keep getting these two errors:

    2/1 PL/SQL: SQL Statement ignored
    2/47 PL/SQL: ORA-00917: missing comma

    I have two tables flat and accomodation and i would like to insert a new record into accomodation after a new one has been inserted into flat,
    with address_type being a collection of objects(address,city and post code)

    Please help

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    1) To specify the inserted values for the trigger table you need to use :NEW:

    insert into x (a, b, c) values (:new.a, :new.b, :new.c);

    2) I almost never use UDTs myself, but I would hope that in this case it would be simply:

    INSERT INTO ACCOMODATION (accomNo,address_type,noOfRooms) VALUES (:new.accomNo,:new.address_type,:new.noOfRooms);

  3. #3
    Join Date
    Feb 2006
    Posts
    17
    SORRY, TRIED THAT BUT STILL GET THE SAME ERRORS!!
    dOES ANYONE KNOW HOW TO DEAL WITH THESE OBJECT TYPES?
    tHANKS

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    NO NEED TO SHOUT

    Well, it works fine for me:
    Code:
    SQL> create type address_type_type as object (address varchar2(30), city varchar2(30), postcode varchar2(10));
      2  /
    
    Type created.
    
    SQL> create table accomodation
      2  (accomNo int, address_type address_type_type, noofrooms int);
    
    Table created.
    
    SQL> create table flat (accomNo int, address_type address_type_type, noofrooms int);
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER accom_insert
      2  AFTER INSERT ON FLAT
      3  FOR EACH ROW
      4  BEGIN
      5  INSERT INTO ACCOMODATION (accomNo,address_type,noOfRooms) VALUES (:new.accomNo,:new.address_type,:new.noOfRooms);
      6* END;
    SQL> /
    
    Trigger created.
    
    SQL> insert into flat values (1, address_type_type('1 High Street', 'London', 'WC1 1AA'), 1);
    
    1 row created.
    
    SQL> select * from accomodation;
    
       ACCOMNO
    ----------
    ADDRESS_TYPE(ADDRESS, CITY, POSTCODE)
    --------------------------------------------------------------------------------
     NOOFROOMS
    ----------
             1
    ADDRESS_TYPE_TYPE('1 High Street', 'London', 'WC1 1AA')
             1

Posting Permissions

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