DB ArvestusTöö: CyberSport - Darja Suhhanova (Portnova)

Description: (Pilet 6)

Используйте SQL Server и localhost/phpMyAdmin
При выполнении заданий используй составление SQL запросов.
Создай таблицы Cybersport (CybersportID, UserName, Team, GameID) и Game (GameID, GameName)
свяжите таблицы между собой.
Создай таблицу logi(id, user, date, data)
Создай триггеры на отслеживание удаления данных в таблице CyberSport и добавления данных в таблицу CyberSport,
при этом в таблице logi должно автоматически фиксироваться имя пользователя, дата и время и данные из таблицы CуberSport и Game, учитывая через INNER JOIN соединение между таблицами
Проверь работу тригеров.

Ticket 6

Use SQL Server and localhost/phpMyAdmin
When completing tasks, use SQL queries.
Create tables Cybersport (CybersportID, UserName, Team, GameID) and Game (GameID, GameName)
Link tables together.
Create a table logi(id, user, date, data)
Create triggers to track the deletion of data in the CyberSport table and the addition of data to the CyberSport table,
at the same time, the logi table should automatically record the user name, date, and data from the CuberSport and Game tables, taking into account the connection between the tables via INNER JOIN
Check the operation of the triggers.

Microsoft SQL Server Management Studio

create table CyberSport (
CybersportID INT PRIMARY KEY identity(1,1), 
UserName varchar(200),
Team varchar(200),
GameID int,
FOREIGN key(GameID) REFERENCES Game(GameID)
);

CREATE TABLE Game (
GameID INT PRIMARY KEY identity(1,1),
GameName varchar(200));

CREATE TABLE logi(
id INT PRIMARY KEY identity(1,1),
user_ VARCHAR(50),
date_ DATE,
data_ TEXT
);

Adding data

CREATE TRIGGER dataAdding_CyberSport
ON CyberSport
AFTER INSERT
AS
BEGIN
    INSERT INTO logi(user_, date_, data_)
    SELECT
	USER,
	GETDATE(),
    CONCAT('Added data: ', i.CybersportID, ': ', i.UserName, ', to the team: ', i.Team, ', ', g.GameID, ': ', g.GameName)
    FROM inserted i
    INNER JOIN Game g
    ON i.GameID = g.GameID;
END

Deleting data

CREATE TRIGGER dataDeleting_CyberSport
ON CyberSport
AFTER DELETE
AS
BEGIN
    INSERT INTO logi(user_, date_, data_)
    SELECT 
	USER,
	GETDATE(),
    CONCAT('Deleted data: ', d.CybersportID, ': ', d.UserName, ', team: ', d.Team, ', ', g.GameID, ': ', g.GameName)
    FROM deleted d
    INNER JOIN Game g
    ON d.GameID = g.GameID;
END

XAMPP

CREATE TABLE Game (
GameID INT PRIMARY KEY AUTO_INCREMENT,
GameName varchar(200)
);

create table CyberSport (
CybersportID INT PRIMARY KEY AUTO_INCREMENT, 
UserName varchar(200),
Team varchar(200),
GameID int,
FOREIGN key(GameID) REFERENCES Game(GameID)
);

CREATE TABLE logi(
id INT PRIMARY KEY AUTO_INCREMENT,
user_ VARCHAR(50),
date_ DATE,
data_ TEXT
);

insert into Game(GameName)
values ('GAME nr 1'), ('GAME nr 15864');
SELECT * FROM Game;

Adding data

insert into CyberSport(UserName, Team, GameID)
values ('Sarah', 'GoodGame', 1);
SELECT * FROM CyberSport;
SELECT * FROM logi;

Deleting data

DELETE FROM cybercport WHERE CybersportID=8;
SELECT * FROM cybercport;
SELECT * FROM logi;