Re: [sqlite] Sqlite Instead of Trigger on Views does not always fire..

[Dan Kennedy]

On 04/02/2014 06:08 PM, Engin Guelen wrote:
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?

Does the WHERE clause on the UPDATE statement match any rows returned by 
the view? And does the UPDATE actually modify column SYNC?






  
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

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users