Commented by: @pcisar

AFAIK this is correct according to SQL standard. Default value is assigned only when no value is provided at all (explicit NULL is a value).

Commented by: Dejan Schild (schild)

It's ok.
But, is it possible to make some solution for this problem in updatable view?
I guess that many people use updatable view for synchronize update on few depended tables, and this rule is limitation in that case.

Maybe to add some directive to INSERT/UPDATE command, like this:

CREATE VIEW VE_KALKROBE(
KALK_UID,
K_CARINA)
AS
select
k.KALK_UID,
k.K_CARINA
from KALKULACIJA k;

CREATE trigger ve_kalkrobe_bi5 for ve_kalkrobe
active before insert position 5
AS
begin
INSERT INTO KALKULACIJA (
KALK_UID,
K_CARINA)
VALUES (
NEW.KALK_UID,
NEW.K_CARINA)
WITH DEFVALUESIFNULL; -- or something similar to tell command to set DEFAULT VALUE to field if provided value is NULL! Also use this directive for UPDATE!
end

CREATE trigger ve_kalkrobe_bu5 for ve_kalkrobe
active before update position 5
AS
begin
UPDATE KALKULACIJA
SET
KALK_UID=NEW.KALK_UID,
K_CARINA=NEW.K_CARINA
WHERE KALK_UID=OLD.KALK_UID
WITH DEFVALUESIFNULL; -- or something similar to tell command to set DEFAULT VALUE to field if provided value is NULL!
end

Naturally, use this only for NOT NULL fields who has default value.

Commented by: Cosmin Apreutesei (cosmin_ap2)

This bit me too, default value are pretty useless in conjunction with updatable views. What's a standard good for if it cannot make its own specified features work together?

AFAIK MSSQL has the keyword default which is a better workaround to the problem, ie. you can say:

insert into t (c1, c2) values (coalesce(new.c1, default), new.c2)

Commented by: @asfernandes

Dmitry, are you going to implement UPDATE ... SET field = DEFAULT or INSERT ... VALUES (x, y, DEFAULT)?

If yes, this seems as same blr verb needed to support ALTER ... ADD ... NOT NULL.

Commented by: @dyemanov

I had such intention, but not in the nearest future. Feel free to jump in and intercept this task if you wish ;-)

Commented by: @mrotteveel

The current request is not in line with the SQL standard, the SQL standard way would be to use the context value DEFAULT, which will use the column default (or NULL if there is no column default).