1. Trigger with Timestamps

DROP TABLE IF EXISTS widgetSale;

DROP TABLE IF EXISTS widgetCustomer;

CREATE TABLE widgetCustomer ( id integer primary key, name TEXT, last_order_id INT, stamp TEXT );

CREATE TABLE widgetSale ( id integer primary key, item_id INT, customer_id INTEGER, quan INT, price INT, stamp TEXT );

CREATE TABLE widgetLog ( id integer primary key, stamp TEXT, event TEXT, username TEXT, tablename TEXT, table_id INT);

INSERT INTO widgetCustomer (name) VALUES ('Bob');

INSERT INTO widgetCustomer (name) VALUES ('Sally');

INSERT INTO widgetCustomer (name) VALUES ('Fred');

SELECT * FROM widgetCustomer;

CREATE TRIGGER stampSale AFTER INSERT ON widgetSale

BEGIN

UPDATE widgetSale SET stamp = DATETIME('now') WHERE id = NEW.id;

UPDATE widgetCustomer SET last_order_id = NEW.id, stamp = DATETIME('now')

WHERE widgetCustomer.id = NEW.customer_id;

INSERT INTO widgetLog (stamp, event, username, tablename, table_id)

VALUES (DATETIME('now'), 'INSERT', 'TRIGGER', 'widgetSale', NEW.id);

END

;

INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (1, 3, 5, 1995);

INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (2, 2, 3, 1495);

INSERT INTO widgetSale (item_id, customer_id, quan, price) VALUES (3, 1, 1, 2995);

SELECT * FROM widgetSale;

SELECT * FROM widgetCustomer;

SELECT * FROM widgetLog;