David He
David He

Reputation: 394

SQLite3 trigger does not work

The trigger defined below does not work in sqlite3.

CREATE TRIGGER 'delete_expired' BEFORE INSERT ON 'visitor' BEGIN DELETE FROM visitor WHERE 'create_at' <= date('now', '-6 day'); END

But, this does... Something might be wrong with the conditions. Can anybody point me out? Thanks in advance

CREATE TRIGGER 'delete_expired' BEFORE INSERT ON 'visitor' BEGIN DELETE FROM visitor; END

Upvotes: 0

Views: 104

Answers (1)

If you need to quote table names and column names, use double quotes, not single quotes.

CREATE TRIGGER 'delete_expired' 
BEFORE INSERT ON visitor 
BEGIN DELETE FROM visitor WHERE create_at <= date('now', '-6 day');
END;

Single quotes usually denote a literal string.

sqlite> select 'create_at' from visitor;
create_at

In an interactive session . . .

sqlite> create table visitor (create_at timestamp);
sqlite>     CREATE TRIGGER 'delete_expired' 
   ...>     BEFORE INSERT ON visitor 
   ...>     BEGIN DELETE FROM visitor WHERE create_at <= date('now', '-6 day');
   ...>     END;
sqlite> insert into visitor values ('2014-01-01');
sqlite> select * from visitor;
2014-01-01
sqlite> insert into visitor values ('2014-11-06');
sqlite> select * from visitor;
2014-11-06

Upvotes: 1

Related Questions