Error in TRIGGER with null value

Database

Error in TRIGGER with null value

Hello,

In this case:

CREATE TABLE t1 (geom ST_GEOMETRY, id PRIMARY KEY not null integer) ;
CREATE TABLE t2 (geom ST_GEOMETRY, id PRIMARY KEY not null integer) ;

CREATE TRIGGER t_ins AFTER INSERT ON t
REFERENCING NEW TABLE AS nt
FOR EACH STATEMENT
BEGIN ATOMIC (
INSERT INTO t2 SELECT geom.ST_Envelope(), id FROM nt WHERE geom IS NOT null;
)
END;

INSERT INTO t (geom, id) VALUES (null,1);

I have the error : The owner expression of the UDM invocation 'ST_ENVELOPE' is not a UDT
What does it mean ?
Why ST_ENVELOPE is executed ?
And how to solve it ?

Thank-you in advance
Stéphane Brunner
Tags (4)
2 REPLIES
Enthusiast

Re: Error in TRIGGER with null value

Sorry for this delayed answer, but your case led to some discussion among the developers as to whether it is a bug. The jury is still out on that question. Personally, I think it
is a bug. The error is generated during the SQL translation phase. Even though ST_Envelope() will not be executed in this case, the SQL compiler still attempts to resolve all the identifier references.

Anyway, a workaround can be suggested. The insert statement will execute correctly if you explicitly cast the null literal to the ST_Geometry type. This actually requires 2 levels of cast because the type has has several casts defined for it; therefore you must first cast to a type that can be matched to a unique cast function. For example:

INSERT INTO t1 (geom, id)
VALUES( CAST(CAST null AS VARCHAR(200)) AS ST_Geometry), 1);

Re: Error in TRIGGER with null value

Hello,

Many thanks for your responds, it's working ;-)

Sincerely
Stéphane Brunner