BTsws35c

1DROP DATABASE IF EXISTS tumblr;
2CREATE DATABASE tumblr CHARSET 'utf8';
3USE tumblr;
4
5CREATE TABLE Blog(
6	Id integer not null auto_increment primary key,
7    name varchar(50) not null,
8    number_of_likes int not null
9);
10
11create table BlogPost(
12	id integer not null auto_increment primary key,
13    name varchar(50) not null,
14    blog_id integer not null ,
15    FOREIGN KEY (blog_id) REFERENCES Blog(Id)
16);
17
18create table Users(
19	id integer not null auto_increment primary key,
20    name varchar(50) not null
21   
22);
23
24create table Likes(
25	id integer not null auto_increment primary key,
26    user_id integer not null ,
27    FOREIGN KEY (user_id) REFERENCES Users(user_id),
28    blog_post integer not null,
29    foreign key (blog_post) references BlogPost(id)
30);
31
32INSERT INTO Users(Id, name) VALUES( 101, 'Зюмбюл Петров');
33INSERT INTO Users(name) VALUES('Яшо');
34INSERT INTO Users(name) VALUES('Simko');
35
36insert into Blog(Id, name, number_of_likes) values (1, 'Simkos blog', 0);
37insert into Blog(name, number_of_likes) values ('Qsho blog', 0);
38insert into Blog(name, number_of_likes) values ('Zyumis blog', 0);
39
40
41insert into BlogPost(Id, name, blog_id) values (1, 'Today I learned', 1);
42insert into BlogPost(name, blog_id) values ('Today I didnt learn', 1);
43insert into BlogPost(name, blog_id) values ('The dangers of gyros', 2);
44insert into BlogPost(name, blog_id) values ('Archer is great', 3);
45insert into BlogPost(name, blog_id) values ('Dragonborn', 3);
46
47
48
49DELIMITER $ 
50
51CREATE TRIGGER Likes_AI AFTER INSERT ON Likes FOR EACH ROW
52BEGIN
53	UPDATE Blog SET number_of_likes = NOW()
54    WHERE Id = OLD.Id AND EndDate IS NULL;
55END$