Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136

    Unanswered: Error creating trigger

    Hello All!
    I have the following problem: compiling this trigger:

    PHP Code:

    CREATE 
    OR REPLACE TRIGGER ECAD_LIC.TRG_VIEW_FEATURE_INSTOF_INS 
    INSTEAD OF INSERT ON ECAD_LIC
    .VIEW_FEATURE 
    BEGIN
        
      INSERT INTO FEATURE
    (ID_FEATUREFEATURE_KEYID_FEATURE_BASE
                             
    VERSIONEXPIRE_DATENUMLIC
                             
    CREATED_ONCREATED_BYCHANGED_ONCHANGED_BY
                        
    SIGNVENDOR_STRINGHOSTIDHOST_BASED
                        
    ISSUEDSUPERSEDE_ISSUEDISSUERSUPERSEDE_ISSUER
                        
    DUPGROUPSUPERSEDE_DUP_GRSNCK
                        
    START_DATEUSER_BASEDTS_OKBORROWFLOAT_OK
                        
    SUITE_DUPGROUPLINGERNOTICEOVERDRAFTPLATFORMS
                  
    VALUES (SEQ_FEATURE.NEXTVAL, :NEW.FEATURE_KEY, :NEW.ID_FEATURE_BASE
                          
    , :NEW.VERSION, :NEW.EXPIRE_DATE, :NEW.NUMLIC
                        
    SYSDATEUSERSYSDATEUSER
                        
    , :NEW.SIGN, :NEW.VENDOR_STRING, :NEW.HOSTID, :NEW.HOST_BASED
                        
    , :NEW.ISSUED, :NEW.SUPERSEDE_ISSUED, :NEW.ISSUER, :NEW.SUPERSEDE_ISSUER
                        
    , :NEW.DUPGROUP, :NEW.SUPERSEDE_DUP_GR, :NEW.SN, :NEW.CK
                        
    , :NEW.START_DATE, :NEW.USER_BASED, :NEW.TS_OK, :NEW.BORROW, :NEW.FLOAT_OK
                        
    , :NEW.SUITE_DUPGROUP, :NEW.LINGER, :NEW.NOTICE, :NEW.OVERDRAFT, :NEW.PLATFORMS);
      
      
    INSERT INTO FEATURE_AVAILABLE (ID_FEATUREFEATURE_KEYID_FEATURE_BASE
                             
    NUM_AVAILEXPIRE_DATE
                        
    CREATED_ONCREATED_BYCHANGED_ONCHANGED_BY
                        
    COMMENTS
                  
    VALUES (SEQ_FEATURE.CURRVAL, :NEW.FEATURE_KEY, :NEW.ID_FEATURE_BASE
                             
    , :NEW.NUM_AVAIL, :NEW.EXPIRE_DATE
                        
    SYSDATEUSERSYSDATEUSER
                        
    , :NEW.COMMENTS);
    END

    i get such error messages:

    - PLS-00049: bad bind variable 'NEW.ID_FEATURE_BASE' (FEATURE)
    - PLS-00049: bad bind variable 'NEW.ID_FEATURE_BASE' (FEATURE_AVAILABLE)
    - PLS-00049: bad bind variable 'NEW.NUM_AVAIL'
    - PLS-00049: bad bind variable 'NEW.COMMENTS'


    But why?Why are all other variables OK and these not?

    Thanks in advance!

    Regards,
    Julia

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The obvious thought it that there are no columns with those names in the view.

  3. #3
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Thank you!

    Now the next:
    ###########################################
    Starting position:
    Table A , Table B
    (FEATURE, FEATURE_AVAILABLE)

    Goal (rough):

    On INSERT data into Table A - INSERT data into Table B
    ###########################################

    What is the best solution:


    1) To create an extra-view containing all columns to do this operation(Goal);

    2) To create a procedure with inserts, and then create a trigger calling this procedure

    ?

    Thanks in advance!
    Regards,
    Julia

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    So whenever a record is inserted into A you want to insert a matching record into B? And from the insert statement on A is there enough information to enable you to create the B record?

    If yes to both questions then an AFTER INSERT trigger on A would make sense.

  5. #5
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello!

    I've created the AFTER_INSERT and AFTER_UPDATE-Triggers, but it doesn't works:
    - INSERT/UPDATE on table A functions
    - INSERT/UPDATE on table B doesn't function
    - Compiling of both triggers works
    - No error-message appears
    - But i don't see any Action on the table B


    Where is my mistake?

    PHP Code:
    CREATE OR REPLACE TRIGGER TRG_A_AFT_INS 
    AFTER INSERT ON A
    REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
           INSERT INTO B 
    (ID
                       
    C1C2C3C4
                      
    CREATED_ONCREATED_BYCHANGED_ONCHANGED_BY
                 
    VALUES (SEQ_A.CURRVAL
                     
    , :OLD.C1, :OLD.C2,  :OLD.C3, :OLD.C4
                     
    SYSDATEUSERSYSDATEUSER);
    END
    and:
    PHP Code:
    CREATE OR REPLACE TRIGGER TRG_A_AFT_UPD 
    AFTER UPDATE ON A
    REFERENCING 
    NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
         UPDATE B    
                 SET C1 
    = :OLD.C1
                  
    C2=:OLD.C2
                     
    C3 =:OLD.C3
                  
    C4 =:OLD.C4
                  
    CREATED_ON SYSDATE
                  
    CREATED_BY USER 
                  
    CHANGED_ON SYSDATE
                  
    CHANGED_BY USER
              WHERE ID 
    = :OLD.ID;
    END

    Thanks in advance!
    Last edited by julla27; 04-30-04 at 09:09.
    Regards,
    Julia

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    "Doesn't work" isn't very informative! Do you get a compilation error, or what?

    The only thing I can see obviously wrong is that you are using :OLD but you should be using :NEW. All :OLD values are NULL during an INSERT.

    (The lines that say "REFERENCING NEW AS NEW OLD AS OLD" is unnecessary by the way.)

  7. #7
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello Andrewst,

    - INSERT/UPDATE on table A functions
    - INSERT/UPDATE on table B doesn't function
    - Compiling of both triggers works
    - No error-message appears
    - But i don't see any Action on the table B

    ?

    Thank you!
    Last edited by julla27; 04-30-04 at 09:10.
    Regards,
    Julia

  8. #8
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hello!

    I've changed :OLD to :NEW as you wrote - it works!Both triggers.

    Thank you very much!
    Regards,
    Julia

Posting Permissions

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