![[info]](http://l-stat.livejournal.com/img/userinfo.gif){kind=small}
Shantanu Oak
Jun. 13th, 2009
05:07 pm - SQLite Triggers
Assuming that customer records are stored in the "customers" table, and that order records are stored in the "orders" table, the following trigger ensures that all associated orders are redirected when a customer changes his or her address:
CREATE TRIGGER update_customer_address UPDATE OF address ON customers
BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
END;
With this trigger installed, executing the statement:
UPDATE customers SET address = '1 Main St.' WHERE name = 'Jack Jones';
causes the following to be automatically executed:
UPDATE orders SET address = '1 Main St.' WHERE customer_name = 'Jack Jones';
![[mood icon]](http://l-stat.livejournal.com/img/mood/classic/smile.gif){kind=small}
happy04:54 pm - SQLite Example file
Here is an example how does SQLite handle datetime function:
create table events (id INTEGER PRIMARY KEY AUTOINCREMENT, name, kind, start, end);
insert into events values (null, 'tom', 'hour', datetime('now', '+1 day','start of day', '+11 hours'), datetime('now', '+1 day','start of day', '+12 hours'));
insert into events values (null, 'tom', 'hour', datetime('now', '+1 day','start of day', '+9 hours'), datetime('now', '+1 day','start of day', '+10 hours'));
insert into events values (null, 'joe', 'hour', datetime('now', '+1 day','start of day', '+9 hours'), datetime('now', '+1 day','start of day', '+10 hours'));
insert into events values (null, 'tom', 'day', datetime('now', '+1 day','start of day'), datetime('now', '+1 day','start of day', '+1 day'));
select *, (strftime('%s', end) - strftime('%s', start)) as length from events;
select *, min((strftime('%s', end) - strftime('%s', start))) as length from events where datetime('now', '+1 day','start of day', '+9 hours','+30 minutes') and end > datetime('now', '+1 day','start of day', '+9 hours','+30 minutes') group by name;
hopeful