Insert (before) trigger does not work.

60 views
Skip to first unread message

Akira Kawaguchi

unread,
Feb 26, 2021, 6:18:13 PM2/26/21
to firebird-support
To avoid inserting an empty string to a TIMESTAMP column of a table, I created a simple trigger to replace it to a NULL value (inserting NULL works).  It turns out before value does not change no matter how I try variations.  A test script follows.  It says -- conversion error from string "", that is, no replacement to null occurs at all.  This is a serious bug...  I appreciate for any help!

CREATE TABLE TestTable (C TIMESTAMP);
SET TERM ^ ;
CREATE TRIGGER G FOR TestTable ACTIVE
-- ALTER TRIGGER G 
BEFORE INSERT AS 
BEGIN 
  IF (NEW.C = '') THEN NEW.C = NULL;
END^
COMMIT^
SET TERM ; ^

INSERT INTO TestTable (C) VALUES ('');
SELECT * FROM TestTable;

Karol Bieniaszewski

unread,
Feb 26, 2021, 6:43:53 PM2/26/21
to firebird...@googlegroups.com

Hi

 

This is not a bug. Field must have value in declared type. If you provide different type it must be converted first before is posted to the engine.

Consider in any programing language with typed variables. How can you store string into integer variabe?

 

Same is with Timestamp.

If you pass string it must be converted first before pass into engine which require field in declared type.

In the trigger you can only manipulate value in field type range.

 

Regards,

Karol Bieniaszewski

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/5c871d3c-e2ac-45cc-ac4e-30b5bda58ed3n%40googlegroups.com.

 

Akira Kawaguchi

unread,
Feb 27, 2021, 2:15:15 AM2/27/21
to firebird...@googlegroups.com
Dear Karol,

Oh, I see. I now understand the reason -- after two days of various futile trials. The trigger does not fire at all due to the value mismatch with the designated type of TIMESTAMP. The engine reported the error before reaching this trigger.  Thanks very much for the clarification. 

In my case, I have to insert some values, including empty strings, into this TIMESTAMP column. I cannot tailor the input data, and therefore I have to find a "database way" to accommodate these empty strings. The solution seems to me is: I rather define VARCHAR(N) instead of TIMESTAMP and then insert all values into it. Then, I nullify empty strings and then alter the column type back to TIMESTAMP.  I hope that this will work out -- I will try now.  Thanks again.

Akira Kawaguchi

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/4DnR7R15dGzlgNms%40smtp.poczta.onet.pl.

Mark Rotteveel

unread,
Feb 27, 2021, 2:30:16 AM2/27/21
to firebird...@googlegroups.com
On 27-02-2021 08:14, Akira Kawaguchi wrote:
> Dear Karol,
>
> Oh, I see. I now understand the reason -- after two days of various
> futile trials. The trigger does not fire at all due to the value
> mismatch with the designated type of TIMESTAMP. The engine reported the
> error before reaching this trigger.  Thanks very much for the
> clarification.
>
> In my case, I have to insert some values, including empty strings, into
> this TIMESTAMP column. I cannot tailor the input data, and therefore I
> have to find a "database way" to accommodate these empty strings. The
> solution seems to me is: I rather define VARCHAR(N) instead of TIMESTAMP
> and then insert all values into it. Then, I nullify empty strings and
> then alter the column type back to TIMESTAMP.  I hope that this will
> work out -- I will try now.  Thanks again.

If you can't tailor the input data, can you tailor how the data is
inserted? E.g massage the data in the insert statement itself, or
replace the insert with a stored procedure, or switch the insert into a
view instead of the table, and add a trigger on the view to insert into
the actual destination table?

Mark
--
Mark Rotteveel

Omacht András

unread,
Feb 27, 2021, 2:36:21 AM2/27/21
to firebird...@googlegroups.com

Hi!

 

You won’t be able to alter varchar to timestamp.

I suggest you create a view that contains all the required fields in the table plus one varchar for the timestamp value as string.

Create a before insert trigger on view and you can write your business logic inside this trigger and there you can fill the table’s fields correctly.

 

András

Vlad Marginean

unread,
Feb 27, 2021, 3:39:30 AM2/27/21
to firebird...@googlegroups.com

Hello Akira,

 

You may try this approach as well :

 

CREATE FUNCTION F_TIMESTAMP_OR_NULL(

  VAL VARCHAR(1000))

RETURNS TIMESTAMP

AS

DECLARE ANYVAR TIMESTAMP;

BEGIN

                BEGIN

                                BEGIN

                                                ANYVAR = CAST(VAL AS TIMESTAMP);

                                END

                                WHEN ANY DO

                                BEGIN

                                                RETURN NULL;

                                END

                END

                RETURN ANYVAR;

END;

 

 

CREATE TABLE TestTable (C TIMESTAMP);

SET TERM ^ ;

 

INSERT INTO TestTable (C) VALUES ( F_TIMESTAMP_OR_NULL(''));

 

INSERT INTO TestTable (C) VALUES ( F_TIMESTAMP_OR_NULL( CAST ('1/1/1970' AS TIMESTAMP)));

 

INSERT INTO TestTable (C) VALUES ( F_TIMESTAMP_OR_NULL( 'SOME_STRING'));

 

INSERT INTO TestTable (C) VALUES ( CURRENT_TIMESTAMP );

 

SELECT * FROM TestTable;

 

Regards,

Vlad

 

 

From: firebird...@googlegroups.com <firebird...@googlegroups.com> On Behalf Of Akira Kawaguchi
Sent: Saturday, February 27, 2021 9:14 AM
To: firebird...@googlegroups.com

Akira Kawaguchi

unread,
Feb 27, 2021, 3:39:51 AM2/27/21
to firebird...@googlegroups.com
Dear Mark,

You are smart and helped me cut the time of my effort. Creating a view and attaching a "before insert" trigger seem to be an excellent solution in this case. I will realize your suggestion and report the result later.  I need to sleep now. I just started using Firebird several days ago, and I now become fond of Firebird much more.  It's a robust DBMS. Thanks again.
Akira Kawaguchi

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/d525ba17-1d03-d78b-e9bc-a93d88f04c66%40lawinegevaar.nl.

Karol Bieniaszewski

unread,
Feb 27, 2021, 5:34:42 AM2/27/21
to firebird...@googlegroups.com

Good concept but function should not catch any convert error to null only from empty string

 

CREATE FUNCTION F_TIMESTAMP_OR_NULL(

  VAL VARCHAR(1000))

RETURNS TIMESTAMP

AS

DECLARE ANYVAR TIMESTAMP;

BEGIN

                IF (VAL=’’) THEN

                  ANYVAR = NULL; ELSE

                   ANYVAR = CAST(VAL AS TIMESTAMP);

               

        RETURN ANYVAR;

END;

 

regards,

Karol Bieniaszewski

Vlad Marginean

unread,
Feb 27, 2021, 6:44:43 AM2/27/21
to firebird...@googlegroups.com

Of course. In this particular case is correct.

It can be written like this also:

 

CREATE OR ALTER FUNCTION F_TIMESTAMP_OR_NULL( VAL VARCHAR(24))

RETURNS TIMESTAMP

AS

BEGIN

RETURN CAST( NULLIF( VAL, '') AS TIMESTAMP );

END

 

In this case:

 

INSERT INTO TestTable (C) VALUES ( F_TIMESTAMP_OR_NULL('X')); --NOT PASSES

INSERT INTO TestTable (C) VALUES ( F_TIMESTAMP_OR_NULL('')); -- PASSES FALLBACK TO NULL

INSERT INTO TestTable (C) VALUES ( F_TIMESTAMP_OR_NULL( CAST ('1/1/1970' AS TIMESTAMP))); --PASSES

INSERT INTO TestTable (C) VALUES ( F_TIMESTAMP_OR_NULL( CURRENT_TIMESTAMP ) ); -- PASSES

 

Regards,

Vlad

Vlad Marginean

unread,
Feb 27, 2021, 6:55:14 AM2/27/21
to firebird...@googlegroups.com

Or just NULLIF, no function at all …

 

From: firebird...@googlegroups.com <firebird...@googlegroups.com> On Behalf Of Karol Bieniaszewski
Sent: Saturday, February 27, 2021 12:35 PM
To: firebird...@googlegroups.com

Vlad Marginean

unread,
Feb 27, 2021, 7:30:44 AM2/27/21
to firebird...@googlegroups.com

The easiest way would be then:

--- NOT SO SAFE ONE LINER ---

INSERT INTO TestTable (C) VALUES( NULLIF( CAST( YOUR_INSERT_VALUE AS VARCHAR(24)) , '' ));

---E.G.

INSERT INTO TestTable (C) VALUES( NULLIF( CAST( '' AS VARCHAR(24)) , '' )); --ok

INSERT INTO TestTable (C) VALUES( NULLIF( CAST( '1/1/1970 00:00:00' AS VARCHAR(24)) , '' )); --ok

INSERT INTO TestTable (C) VALUES( NULLIF( CAST( CURRENT_TIMESTAMP AS VARCHAR(24)) , '' )); --ok

INSERT INTO TestTable (C) VALUES( NULLIF( CAST( 'X' AS VARCHAR(24)) , '' )); -- error

 

IMO I would opt for the function as there would be a better control for any transformation. But depends of course on each use case.

 

Regards,

Vlad

Akira Kawaguchi

unread,
Feb 27, 2021, 12:54:40 PM2/27/21
to firebird...@googlegroups.com
Dear Karol, Mark, Omacht, and Vlad:
Thank you all for giving me your thoughtful resolutions.  So, I tested after creating a view on a target table and inserting an empty string to a TIMESTAMP column of the view structure, hoping that the trigger can capture the empty value before going to the target table.  This method will not solve the problem because the view's column definition still comes from the target table.  The insertion operation ended up with 'conversion from "" error.'

After pondering a short while, I found a solution: I converted the TIMESTAMP column X as VARCHAR(N) type in the target table. I also added another TIMESTAMP column Y, with a default value of NULL, to the target table.  I created a "before insert" trigger to check if (NEW.X <> '') then  NEW.Y = NEW.X; that is, Y is populated by either a non-empty valid date or NULL.  After completing insertion operations, I just need to alter the target table to drop the column X and rename Y to X.

What I am doing in this project is to execute a very long insert SQL script that is produced by other DBMS such as MariaDB and SQLite3 to migrate their data instances to Firebird. I developed a simple yet powerful script translator to allow me to run the translated script at Firebird smoothly.  I am sure that your CAST and Function approach should be the solution if I were inclined to modify the script translator's logic to migrate other table data from other databases.  I thought about my additional effort to develop a generalized translator, and I concluded that the above approach for adding a new column would be the simplest solution and serve my needs this time.   Thank you all once again.  I attached a test script below:

CREATE TABLE TESTTABLE (C VARCHAR(24), T TIMESTAMP DEFAULT NULL);
COMMIT;

SET TERM ^ ;
CREATE TRIGGER G FOR TESTTABLE ACTIVE
BEFORE INSERT AS 
BEGIN 
  IF (NEW.C <> '') THEN NEW.T = NEW.C;
END^

COMMIT^

SET TERM ; ^

INSERT INTO TESTTABLE (C) VALUES ('');
INSERT INTO TESTTABLE (C) VALUES ('');
INSERT INTO TESTTABLE (C) VALUES ('');
INSERT INTO TESTTABLE (C) VALUES('2020-10-10 12:17:31');
INSERT INTO TESTTABLE (C) VALUES ('');
COMMIT;

SELECT * FROM TESTTABLE;
COMMIT;

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/000e01d70d03%24fa915350%24efb3f9f0%24%40softwiz.ro.

Omacht András

unread,
Feb 27, 2021, 1:47:46 PM2/27/21
to firebird...@googlegroups.com

Hi!

 

Here is my suggested solution. I think you misunderstood a little bit. The trick is TIMES_AS_VARCHAR field.

 

CREATE TABLE TEST_TABLE (

    ID     INTEGER,

    TIMES  TIMESTAMP

);

 

 

CREATE OR ALTER VIEW TEST_VIEW(

    ID,

    TIMES,

    TIMES_AS_VARCHAR)

AS

select id, times, cast(null as varchar(40))  from test_table;

 

 

CREATE OR ALTER TRIGGER TEST_VIEW_BI0 FOR TEST_VIEW

ACTIVE BEFORE INSERT POSITION 0

as

declare variable ts type of column test_table.times;

begin

  begin

    ts = cast(new.times_as_varchar as timestamp);

  when any do

    begin

      ts = null;

    end

  end

 

  insert into test_table(id, times) values (new.id, :ts);

end

 

 

Test it:

insert into test_view(id, times_as_varchar) values (1, null);

insert into test_view(id, times_as_varchar) values (2, '');

insert into test_view(id, times_as_varchar) values (3, '2021.02.28');

insert into test_view(id, times_as_varchar) values (4, '2021.02.28 19:42:00');

commit;

 

select * from test_table order by id

 

Result:

ID         TIMES

1          <NULL>

2          <NULL>

3          28.02.2021 00:00

4          28.02.2021 19:42

 

select * from test_view order by id

ID         TIMES  TIMES_AS_VARCHAR

1          <NULL>            <NULL>

2          <NULL>            <NULL>

3          28.02.2021 00:00          <NULL>

4          28.02.2021 19:42          <NULL>

 

 

András

Svein Erling Tysvær

unread,
Feb 27, 2021, 2:00:04 PM2/27/21
to firebird...@googlegroups.com
Why not simplify by having two tables? One with all fields defined as varchar and one with the fields defined with their proper type. Data should be inserted into the table with all fields defines as varchar and then an AFTER INSERT trigger on that table should populate the table where things have their proper types.

HTH,
Set

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/f40e64ace36f430aa1705c8efdbbeaa3%40mail3.mve.hu.

Omacht András

unread,
Feb 27, 2021, 2:05:05 PM2/27/21
to firebird...@googlegroups.com

Hi!

 

To avoid unnecessary disk writing. (If that matters.)

 

András

CÉGÜNK A LIBRA CSOPORT TAGJA

OMACHT ANDRÁS
fejlesztési igazgató

LIBRA Szoftver Zrt.
1113 Budapest, Karolina út 65.
+36 (1) 255-3939

in...@libra.hu | www.libra.hu

Dimitry Sibiryakov

unread,
Feb 27, 2021, 3:02:29 PM2/27/21
to firebird...@googlegroups.com
27.02.2021 20:05, Omacht András wrote:
> To avoid unnecessary disk writing. (If that matters.)

With GTT it doesn't matter.

--
WBR, SD.

Akira Kawaguchi

unread,
Feb 27, 2021, 4:54:10 PM2/27/21
to firebird...@googlegroups.com
Dear Omacht, 

Oh, I see.  This sounds like an excellent and beautiful solution that requires no update to the target table.  Amazing.  Thanks so much.  I will test yours now.  I also have one another problem regarding a conversion from an empty string to an integer value.  I will test these and report you the result.  

Also, am I correct that there is no general import utility from a CSV file to a Firebard table?  Thanks again.

Akira Kawaguchi

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/427098e6-380f-78c6-bcad-49ebbc6ae0a1%40ibphoenix.com.

Dimitry Sibiryakov

unread,
Feb 27, 2021, 5:17:22 PM2/27/21
to firebird...@googlegroups.com
27.02.2021 22:42, Akira Kawaguchi wrote:
> Also, am I correct that there is no general import utility from a CSV file to a
> Firebard table?

From google: http://www.guacosoft.com/xmlwizard/

--
WBR, SD.

Akira Kawaguchi

unread,
Feb 28, 2021, 11:53:19 AM2/28/21
to firebird...@googlegroups.com
Dear Omacht and Dimitry:

Thanks so much for the excellent solution to minimize and optimize the work of my project. I verified Omacht's script.  Attached, I slightly modified it to cope with the additional empty string insertion problem (this time integer/decimal insertion with an empty string).  As shown, adding a default null value to an introduced variable can ignore cast failure -- just a minor extension.  I think Firebird's treatment of empty string (as a failure) for the string type is a bit too strict but correct in its semantics.  For instance, MySQL/MariaDB treats "" for integer type as 0 which is not sound in principle.  I learned a lot in these few days, especially a trick to adopt a (null) view with a set of arbitrary data types for adding a before-insert "instead of" trigger.  Thank you.

Also, thank you, Dimitry for the link to XMLWizard which seems to do the work in the Windows environment.  I am developing a fully distributed database application that relies on several open-source DBMS and therefore data type compatibility and instant migration among DBMSs is a matter to realize. You guys helped me a lot.  Thank you once again. 

Akira Kawaguchi.

RECREATE TABLE TEST_TABLE (
    ID DECIMAL(10),
    TM TIMESTAMP
);
RECREATE VIEW TEST_VIEW (ID, TM) AS
    SELECT CAST(NULL AS VARCHAR(10)), CAST(NULL AS VARCHAR(40)) 
    FROM TEST_TABLE;
COMMIT;

SET TERM ^ ;
RECREATE TRIGGER TEST_TRIGGER FOR TEST_VIEW ACTIVE
BEFORE INSERT AS 
DECLARE VARIABLE VAR_ID TYPE OF COLUMN TEST_TABLE.ID = NULL;
DECLARE VARIABLE VAR_TM TYPE OF COLUMN TEST_TABLE.TM = NULL;
BEGIN 
  BEGIN
    BEGIN 
      VAR_ID = CAST(NEW.ID AS TYPE OF COLUMN TEST_TABLE.ID);
      WHEN ANY DO BEGIN END -- simply do nothing if cast fails.
    END
    BEGIN 
      VAR_TM = CAST(NEW.TM AS TYPE OF COLUMN TEST_TABLE.TM);
      WHEN ANY DO BEGIN END -- simply do nothing if cast fails.
    END
  END
  INSERT INTO TEST_TABLE(ID, TM) VALUES (:VAR_ID, :VAR_TM);
END^
COMMIT^

SET TERM ; ^

INSERT INTO TEST_VIEW (ID, TM) VALUES (1, '');
INSERT INTO TEST_VIEW (ID, TM) VALUES (2, '');
INSERT INTO TEST_VIEW (ID, TM) VALUES (3, '2020-10-10 12:17:31');
INSERT INTO TEST_VIEW (ID, TM) VALUES ('','2020-10-10 12:17:31');
INSERT INTO TEST_VIEW (ID, TM) VALUES ('','');
COMMIT;

SELECT * FROM TEST_TABLE;
SELECT * FROM TEST_VIEW;
COMMIT;

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages