Results 1 to 14 of 14

Thread: trigger errors

  1. #1
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    Unanswered: trigger errors

    This top trigger was already created when i was doin the datablocks i added another trigger at the bottom!


    --
    -- Begin default enforce data integrity constraint APPTYPEID_FK section
    --
    declare
    cursor primary_cur is select 'x' from DAVIDSMALE.APPTYPE
    where APPTYPEID = :APPOINTMENT.APPTYPEID;
    primary_dummy char(1);
    begin
    if ( ( :APPOINTMENT.APPTYPEID is not null ) ) then
    open primary_cur;
    fetch primary_cur into primary_dummy;
    if ( not primary_cur%found ) then
    message('Foreign key value does not currently exist in the primary key table.');
    close primary_cur;
    raise form_trigger_failure;
    end if;
    close primary_cur;
    end if;
    end;
    --
    -- End default enforce data integrity constraint APPTYPEID_FK section
    --

    !i have created the trigger below this trigger!!

    Declare ENDDATETIME DATE;
    Startdatetime DATE;

    Begin
    IF :APPOINTMENT.APPTYPEID=1 THEN
    INSERT INTO ENDDATETIME
    select SUM(substr(Startdatetime, 12 , 5)) + 10 FROM Appointment;
    end if;
    end;

    but when i come to run it it still gives me an error sayin it wants me to decleare ENDDATETIME??

    Any Ideas?

  2. #2
    Join Date
    Nov 2003
    Location
    Rotterdam, Netherlands
    Posts
    127
    u r trying to insert into a table called ENDDATETIME?
    why did u declare a DATE variable of that name?

    Either u don't have access to the table with that name or the table dosenot exist.

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Do you want the date value in ENDDATETIME? If so you should be doing a SELECT INTO not an INSERT

  4. #4
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    but...

    the ENDDTAETIME isnt a table it is a field in the datablock we created which is drawn from the appointment table.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Then "SELECT INTO" is what you want

  6. #6
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    tryed it but...

    Tryed it but we got an error at "INTO"

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    and the error would be ?????

  8. #8
    Join Date
    Nov 2003
    Location
    england
    Posts
    95
    encounterd the symbol "into" when expected on of the following:

    (*-+ all case mod null <a bind variable>
    <a double-quoted delimited-identifier> <a bind variable> etc

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Originally posted by lucyg_2000
    encounterd the symbol "into" when expected on of the following:

    (*-+ all case mod null <a bind variable>
    <a double-quoted delimited-identifier> <a bind variable> etc
    Are you doing this??

    select SUM(substr(Startdatetime, 12 , 5)) + 10
    INTO ENDDATETIME
    FROM Appointment;

    That will get your value in the correct variable, then put data in your data base.

    Check out proper INSERT structure also if you want to do it all at once (VALUES, use of () etc. )

    I need to run to a meeting. Hope this helps. I will check back later.

  10. #10
    Join Date
    Nov 2003
    Location
    england
    Posts
    95

    re...

    No i hadnt tryed that but I did but it now gives me an error type mismatch found at 'ENDDATTIME' in SELECT...INTO statement.

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Declare ENDDATETIME DATE;

    Your are trying to put a numeric value in enddatetime (a date variable)
    select SUM(substr(Startdatetime, 12 , 5)) + 10 ...

    Try
    Declare ENDDATETIME number;
    then
    select SUM(substr(Startdatetime, 12 , 5)) + 10 ...

    HTH
    Gregg

  12. #12
    Join Date
    Nov 2003
    Location
    england
    Posts
    95
    it is a dattime field

  13. #13
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Then you will need to change the result to a date instead of a
    number

    select to_date(SUM(substr(Startdatetime, 12 , 5))+ 10 ,'DD-MON-YYYY HH24:MIS') INTO ENDDATETIME FROM Appointment;

    hth
    Gregg

  14. #14
    Join Date
    Nov 2003
    Location
    england
    Posts
    95
    it is a dattime field

    dont worry we have given up on this now.... it has to hannded in in less that 24 hours and we have other bits to do!!!!!!

    Cheers for ya help though!!!!

Posting Permissions

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