LRKFYpig

1drop table if exists event;
2
3create table event
4(
5event_id int unsigned not null auto_increment primary key,
6name varchar(255) not null,
7event_date datetime not null,
8created_date datetime not null,
9last_updated timestamp default current_timestamp on update current_timestamp 
10);
11
12delimiter #
13
14create trigger event_before_ins_trig before insert on event
15for each row
16begin
17 set new.created_date = now();
18end#
19
20delimiter ;
21
22
23insert into event (name, event_date) values 
24('event1', now() - interval 3 day), 
25('event2', now() - interval 2 day), 
26('event3', now() - interval 1 day),
27('event4', now() + interval 1 day);
28
29select e.*, unix_timestamp(e.last_updated) from event e;
30
31/*
32-- wait a while then run
33
34update event set name = 'event one' where event_id = 1;
35
36select e.*, unix_timestamp(e.last_updated) from event e;
37*/