[sqlite] a TRIGGER to create a VIEW ? (newbie)

[Andreas]

Hi sqlite-users,

i'm working on a web-based workflow involving POW and Server-Side-JS  
and sqlite for sure.
I'm trying to let a Trigger create a View. The first Trigger 
('resetSyncMarker') cares for unique-keying in the config-table. The  
second Trigger('makeView') shall create a View on insert into the  
config-table. The View's name shall be the content of the inserted  
shortName-field. The View shall select all rows in the files-table  
WHERE mand-field=shortName-field.

BEGIN TRANSACTION;
CREATE TABLE config (
        client VARCHAR,
        isSynced INTEGER DEFAULT 0,
        shortName CHAR(4),
        remoteIndex VARCHAR,
        localIndex VARCHAR,
        lastCheck DATE DEFAULT CURRENT_DATE,
        lastCheckSTR DATE DEFAULT CURRENT_DATE,
        PRIMARY KEY (shortName) ON CONFLICT REPLACE
);
CREATE TABLE files (
        mand CHAR(4),
        name VARCHAR,
        mailDate DATE DEFAULT CURRENT_DATE,
        liveDate DATE DEFAULT 0,
        pubDate DATE DEFAULT 0,
        title VARCHAR,
        PRIMARY KEY (mand, name) on CONFLICT REPLACE
);
CREATE TRIGGER resetSyncMarker INSERT ON files
        BEGIN
                UPDATE config SET isSynced = 0 WHERE shortName = new.mand;
        END;
# begin of problem

CREATE TRIGGER makeView INSERT ON config
        BEGIN
		CREATE VIEW new.shortName AS SELECT * FROM files WHERE  
mand=new.shortName;
        END;
# eo-problem

COMMIT;

That gives me syntax-error's for the CREATE-statement inside the  
CREATE TRIGGER-statement. I've tried variations, taken from the  
mailing list:

CREATE VIEW new."shortName" AS SELECT * FROM files WHERE  
mand=new."shortName";
CREATE VIEW quote(new.shortName) AS SELECT * FROM files WHERE  
mand=quote(new.shortName);

Is this doable at all and maybe quoting/escaping is the problem ? Or  
is it a goofy concept ?

thanks for the support, greets




-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------