Reputation: 394
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
Reputation: 95542
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