CODEWITHSUNDEEP
javascriptsqlitetriggersinsert
JMarc
JMarc

Reputation: 1004

how to write a sqlite trigger in JS wrapper

Let's assume 2 tables: table1[idTble1, elmt1_T1, elmt2_T1] table2[idTble2, id_Tble1, elmt1_T2, index] I want to create a trigger that runs after Insert On table1, and Insert a new row in Table2 where 

id_Tble1 = new.idTble1
elmt1_T2 = new.elmt1_T1

and index is a value set by the user and that is only saved in Table2. I have tried the following 

var createTrigger = "CREATE TRIGGER triggerInsert AFTER INSERT ON Table1 REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW BEGIN INSERT INTO Table2 (id_Tble1, elmt1_T2, index) VALUES (:new.idTble1, :new.elmt_T1, ?); END;";
db.transaction(function(tx) {
    tx.executeSql(createTrigger, [i], null, onError);
    });

but i got an error (could not prepare statement (1 near "REFERENCING": syntax error).

Upvotes: 1

Views: 1348

Answers (1)

JMarc
JMarc

Reputation: 1004

After several looooong hours, I got it working, at least partially. I have to admit that the software SQLiteManager, even in demo version, was helpful in debugging the code. Here is the trigger that works:

var createTrigger = "CREATE TRIGGER triggerInsert AFTER INSERT ON Table1 BEGIN INSERT INTO Table2 (id_Tble1, elmt1_T2, index) VALUES (new.idTble1, new.elmt_T1, new.index); END";
db.transaction(function(tx) {
    tx.executeSql(createTrigger, [], null, onError);
    });

When I say partially, is because I have not been able to have the trigger use the value of 'index' in the form. So what I had to do was to create a new field (index) in table1.

Upvotes: 1

Related Questions