Hello,
i noticed that Instead of Update Triggers on Views do not fire as soon as the
Views get a bit more involved.
The Following View and Trigger Code compiles w/o Errors.
But when changing the SYNC column nothing happens to REF_TAB, that means the
trigger does not fire.
A drastically simpler View Definition in contrast does what i expect in a
similar context.
Does anybody have an idea what might be the issue?
Regards,
Engin
---------------------------------------------------------------------
CREATE VIEW X_DEV2REF
AS
SELECT 'x' SYNC,C.ENV ENV,'' TV,C.SCHEMA SCHEMA,C.TAB TAB,'' COL,'' PF,'' U,''
NN,'' INC,'' CACHE,'' TYPE,'' LENGTH,'' SCALE,IFNULL(S.ALTERED,R.CREATED)
ALTERED
FROM
(
SELECT ENV,SCHEMA,TAB
FROM
(
SELECT 'DEV' ENV,SCHEMA,TAB
FROM ENV_TAB
WHERE ENV = 'DEV'
EXCEPT
SELECT 'DEV' ENV,SCHEMA,TAB
FROM REF_TAB
)
UNION ALL
SELECT ENV,SCHEMA,TAB
FROM
(
SELECT 'REF' ENV,SCHEMA,TAB
FROM REF_TAB
EXCEPT
SELECT 'REF' ENV,SCHEMA,TAB
FROM ENV_TAB
WHERE ENV = 'DEV'
)
) C
LEFT JOIN ENV_TAB S ON (S.ENV=C.ENV AND S.TAB=C.TAB)
LEFT JOIN REF_TAB R ON (C.ENV='REF' AND R.TAB=C.TAB)
union
SELECT 'x'
SYNC,C.ENV,C.TV,C.SCHEMA,C.TAB,C.COL,C.PF,C.U,C.NN,C.INC,C.CACHE,C.TYPE,C.LENGTH,C.SCALE,T.ALTERED
FROM
(
SELECT ENV,TV,SCHEMA,TAB,PF,U,COL,NN,INC,CACHE,TYPE,LENGTH,SCALE
FROM
(
SELECT 'DEV' ENV,TV,SCHEMA,TAB,PF,U,COL,NN,INC,CACHE,TYPE,LENGTH,SCALE
FROM ENV_COL
WHERE ENV = 'DEV'
EXCEPT
SELECT 'SIT' ENV,TV,SCHEMA,TAB,PF,U,COL,NN,INC,CACHE,TYPE,LENGTH,SCALE
FROM REF_COL
)
WHERE TAB NOT IN (SELECT TAB FROM X_DEV2SIT_TAB)
UNION ALL
SELECT ENV,TV,SCHEMA,TAB,PF,U,COL,NN,INC,CACHE,TYPE,LENGTH,SCALE
FROM
(
SELECT 'REF' ENV,TV,SCHEMA,TAB,PF,U,COL,NN,INC,CACHE,TYPE,LENGTH,SCALE
FROM REF_COL
EXCEPT
SELECT 'DEV' ENV,TV,SCHEMA,TAB,PF,U,COL,NN,INC,CACHE,TYPE,LENGTH,SCALE
FROM ENV_COL
WHERE ENV = 'DEV'
)
WHERE SCHEMA||'.'||TAB NOT IN
(
SELECT DISTINCT SCHEMA||'.'||TAB
FROM
(
SELECT ENV,SCHEMA,TAB
FROM
(
SELECT 'DEV' ENV,SCHEMA,TAB
FROM ENV_TAB
WHERE ENV = 'DEV'
EXCEPT
SELECT 'DEV' ENV,SCHEMA,TAB
FROM REF_TAB
)
UNION ALL
SELECT ENV,SCHEMA,TAB
FROM
(
SELECT 'REF' ENV,SCHEMA,TAB
FROM REF_TAB
EXCEPT
SELECT 'REF' ENV,SCHEMA,TAB
FROM ENV_TAB
WHERE ENV = 'DEV'
)
)
)
) C
JOIN ENV_TAB T ON (T.ENV=C.ENV AND C.TAB=T.TAB)
ORDER BY TV,TAB,COL,ENV;
CREATE TRIGGER TR_X_DEV2REF_01 INSTEAD OF UPDATE of SYNC ON X_DEV2REF
BEGIN
delete from REF_TAB where 1=1; -- Just to Check if the Trigger fired..
END;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
