#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    3
    Rep Power
    0

    Error in trigger while using insert


    Hey there,

    I'm new in using firebird, I'm trying to convert adatabase from MSSql to firebird. Now I have a error with a trigger,
    SET TERM ^ ;

    CREATE TRIGGER "TRIG_TIJDRG_LOGGING_BI" FOR "TIJDRG_LOGGING"
    BEFORE INSERT
    AS
    DECLARE BARCODE VARCHAR(12);
    DECLARE ORDERNR NUMERIC;
    DECLARE BLADNR NUMERIC(15, 0);
    BEGIN
    IF(NEW."LOG_ID" IS NULL) THEN NEW."LOG_ID" = GEN_ID("GEN_TIJDRG_LOGGING_LOG_ID",1);
    BARCODE=NEW.BARCODE;
    IF (NEW.SCANNER <> 0 AND STRLEN(BARCODE)=11) THEN
    BEGIN
    IF (SUBSTRLEN(BARCODE,1,2) = '10') THEN
    BEGIN
    For Select ordernr,bladnr From Prodorder Where Paneelnr=(SUBSTRLEN(NEW.BARCODE,1,8) || ',' || SUBSTRLEN(NEW.BARCODE,9,3))
    INTO ORDERNR,BLADNR
    DO
    BEGIN
    Insert Into tijdrg_logging_ds (scanner,ordernr,bladnr,datum) VALUES (NEW.SCANNER,:ORDERNR,:BLADNR,NEW.DATUM);
    END
    END
    END
    END
    ^

    COMMIT WORK ^
    SET TERM ;^

    Why is the INSERT INTO command wrong by using the variables :ORDERNR and :BLADNR
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by boso2369
    Hey there,

    I'm new in using firebird, I'm trying to convert adatabase from MSSql to firebird. Now I have a error with a trigger,
    Code:
    SET TERM ^ ;
    
    CREATE TRIGGER "TRIG_TIJDRG_LOGGING_BI" FOR "TIJDRG_LOGGING"
    BEFORE INSERT
    AS
    DECLARE BARCODE VARCHAR(12);
    DECLARE ORDERNR NUMERIC;
    DECLARE BLADNR NUMERIC(15, 0);
    BEGIN
          IF(NEW."LOG_ID" IS NULL) THEN NEW."LOG_ID" = GEN_ID("GEN_TIJDRG_LOGGING_LOG_ID",1);
          BARCODE=NEW.BARCODE;
          IF (NEW.SCANNER <> 0 AND STRLEN(BARCODE)=11) THEN
          BEGIN
               IF (SUBSTRLEN(BARCODE,1,2) = '10') THEN
               BEGIN
                    For Select ordernr,bladnr From Prodorder Where Paneelnr=(SUBSTRLEN(NEW.BARCODE,1,8) || ',' || SUBSTRLEN(NEW.BARCODE,9,3))
                    INTO :ORDERNR,:BLADNR
                    DO
                    BEGIN
                         Insert Into tijdrg_logging_ds (scanner,ordernr,bladnr,datum) VALUES (NEW.SCANNER,:ORDERNR,:BLADNR,NEW.DATUM);
                    END
               END
          END
    END
     ^
    
    COMMIT WORK ^
    SET TERM ;^
    Why is the INSERT INTO command wrong by using the variables :ORDERNR and :BLADNR
    I'm not sure is that an error and is this the solution for your problem but use ':' before the variables when you retun values in them.
    What is the exact error message?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    3
    Rep Power
    0
    the error is
    Statement #3:
    invalid request BLR at offset 789
    undefined message number

    I'm sure that the :OrderNr in the INsert into command create the error because when I change tis to 0, everything works fine
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    12
    Originally Posted by boso2369
    the error is
    Statement #3:
    invalid request BLR at offset 789
    undefined message number

    I'm sure that the :OrderNr in the INsert into command create the error because when I change tis to 0, everything works fine
    Is the data type the same in all tables as the type in the trigger?
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2010
    Posts
    3
    Rep Power
    0
    Yes, I cheked this.

IMN logo majestic logo threadwatch logo seochat tools logo