Ülesanne Bogdan Sergachev

ÜLESANNE:

Andmebaasinimi – raamatukogu
tabelinimi, andmete sisestamiseks ja väljade nimed tabelis, nt. Loo tabel raamatu (raamatuID, raamatuNimi, raamatuHind),
raamatuBroneeri(raamatuID, ostjaNimi, ostjaPerenimi)
Loo tabel trigerite töö salvestamiseks. Näiteks tabel logi (logiID, kuupaev, andmed, kasutaja).
Trigerinimed ja mida triger peab tegema seoses eelnevalt loodud tabeliga. – Insert, delete
Kasutajanimi, kes saab töötada ainult varem loodud tabeliga (mitte logi tabel) – Kasutajanimi: Juht, parool: 123456, tabel: raamatuBroneri

LUUA DATABASE:

CREATE DATABASE raamatukogu;
USE raamatukogu;

LUUA TABLES:

CREATE TABLE raamatu(
raamatuID INT IDENTITY PRIMARY KEY,
raamatuNimi VARCHAR(60),
raamatuHind INT);
CREATE TABLE raamatuBroneeri(
raamatuID INT IDENTITY PRIMARY KEY,
ostjaNimi VARCHAR(60),
ostjaPerenimi VARCHAR(60));
CREATE TABLE logi(
logiID INT PRIMARY KEY IDENTITY(1,1),
kuupaev DATETIME,
sisestatudAndmed TEXT,
kasutaja VARCHAR(100));

CREATE TRIGGERS:

CREATE TRIGGER raamatuBroneeriLisamine
ON raamatuBroneeri --tabel, mis jälgitakse
FOR INSERT
AS
INSERT INTO logi(kuupaev, sisestatudAndmed, kasutaja)
SELECT
GETDATE(),
CONCAT('lisatud andmed ', inserted.ostjaNimi, ', ',inserted.ostjaPerenimi),
USER
FROM inserted

CREATE TRIGGER raamatuBroneeriKustutamine
ON raamatuBroneeri --tabel, mis jälgitakse
FOR DELETE
AS
INSERT INTO logi(kuupaev, sisestatudAndmed, kasutaja)
SELECT
GETDATE(),
CONCAT('lisatud Kustutamine: ', deleted.ostjaNimi, ', ',deleted.ostjaPerenimi),
USER
FROM deleted

CREATE TRIGGER raamatuBroneeriUuendamine
ON raamatuBroneeri --tabel, mis jälgitakse
FOR UPDATE 
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
GETDATE(),
CONCAT('Kustutatud andmed: ', deleted.ostjaNimi, ', ', deleted.ostjaPerenimi,
' Uued andmed: ',inserted.ostjaNimi, ', ', inserted.ostjaPerenimi),
USER
FROM deleted INNER JOIN inserted
ON deleted.raamatuId=inserted.raamatuId

KONTROLL TRIGGERS:

--kontrollimiseks lisame raamatuBroneeri
INSERT INTO raamatuBroneeri(ostjaNimi, ostjaPerenimi)
VALUES ('Martin', 'Sild');
SELECT * FROM raamatuBroneeri;
SELECT * FROM logi;


--kontrollimiseks kustutame raamatu tabelis raamatuBroneeri
DELETE FROM raamatuBroneeri
WHERE raamatuId=5;
SELECT * FROM raamatuBroneeri;
SELECT * FROM logi;

--kontrollimiseks uuendame raamatuBroneeri
SELECT * FROM raamatuBroneeri;
UPDATE raamatuBroneeri SET ostjaNimi='Hleb', ostjaPerenimi='Bronja'
WHERE raamatuId=3;
SELECT * FROM raamatuBroneeri;
SELECT * FROM logi;

CREATE USER:

Ta saab kirjutada käsud ainult tabelis raamatu Brooneeri;

Raamatukogu seos andmebaas

Luuame andmebaas

--luua database 
CREATE DATABASE raamatukogu;
USE raamatukogu;

Luuame tabelit

--luua tabelit

CREATE TABLE raamatu(
raamatuID INT IDENTITY PRIMARY KEY,
raamatuNimi VARCHAR(60),
raamatuHind INT);
insert into raamatu(raamatuNimi,raamatuHind)
Values ('Kampsun ja päike', 27);
insert into raamatu(raamatuNimi,raamatuHind)
Values ('Kaka ja kevad', 13);
insert into raamatu(raamatuNimi,raamatuHind)
Values ('Vanaema õunapuu otsas', 7);


CREATE TABLE raamatuBroneeri(
raamatuBroneeriID INT IDENTITY PRIMARY KEY,
ostjaNimi VARCHAR(60),
ostjaPerenimi VARCHAR(60));

CREATE TABLE logi(
logiID INT PRIMARY KEY IDENTITY(1,1),
kuupaev DATETIME,
sisestatudAndmed TEXT,
kasutaja VARCHAR(100));

Tegime seosed kahe tabeliga

ALTER TABLE raamatuBroneeri ADD raamatuID int;
ALTER TABLE raamatuBroneeri ADD CONSTRAINT fk_raamatu
FOREIGN KEY (raamatuID) REFERENCES raamatu(raamatuID)

loome trigeri raamatuBroneeriLisamine

CREATE TRIGGER raamatuBroneeriLisamine
ON raamatuBroneeri --tabel, mis jälgitakse
FOR INSERT
AS
INSERT INTO logi(kuupaev, sisestatudAndmed, kasutaja)
SELECT
GETDATE(),
CONCAT('lisatud andmed ', inserted.ostjaNimi, ', ',inserted.ostjaPerenimi, ', ', raamatu.raamatuNimi),
USER
FROM inserted
Inner join raamatu 
ON raamatu.raamatuID= inserted.raamatuBroneeriID

Kontrollime

INSERT INTO raamatuBroneeri(ostjaNimi, ostjaPerenimi, raamatuID)
VALUES('Bogdan', 'Sats', 3);
Select * from logi;
select * from raamatuBroneeri;

loome trigeri raamatuBroneeriKustutamine

CREATE TRIGGER raamatuBroneeriKustutamine
ON raamatuBroneeri --tabel, mis jälgitakse
FOR DELETE
AS
INSERT INTO logi(kuupaev, sisestatudAndmed, kasutaja)
SELECT
GETDATE(),
CONCAT('kustutatud andmed ', deleted.ostjaNimi, ', ',deleted.ostjaPerenimi, ', ', raamatu.raamatuNimi),
USER
FROM deleted
Inner join raamatu 
ON raamatu.raamatuID= deleted.raamatuBroneeriID

kontrollimiseks kustutame raamatu tabelis raamatuBroneeri

DELETE FROM raamatuBroneeri
WHERE raamatuBroneeriID=3;
SELECT * FROM raamatuBroneeri;
SELECT * FROM logi;

Loome trigeri raamatuBroneeriUuendamine

CREATE TRIGGER raamatuBroneeriUuendamine
ON raamatuBroneeri --tabel, mis jälgitakse
FOR UPDATE
AS
INSERT INTO logi(kuupaev, sisestatudAndmed, kasutaja)
SELECT
GETDATE(),
CONCAT(
'vanad andmed - ', deleted.ostjaNimi, ', ',deleted.ostjaPerenimi, ', ', r1.raamatuNimi,
'uuendatud andmed - ',inserted.ostjaNimi, ', ',inserted.ostjaPerenimi, ', ', r2.raamatuNimi),
USER
FROM deleted
Inner join inserted ON deleted.raamatuID= inserted.raamatuID
Inner join raamatu r1 On deleted.raamatuID=r1.raamatuID
Inner join raamatu r2 On inserted.raamatuID=r2.raamatuID

kontrollimiseks uuendamine

--kontroll
UPDATE raamatuBroneeri SET ostjaNimi='Ramas',ostjaPerenimi='Saar', raamatuID=3
WHERE raamatuBroneeriID=2;

SELECT * FROM raamatuBroneeri;
SELECT * FROM logi;

Loome User:

Ta saab kirjutada käsud ainult tabelis raamatu Brooneeri; (GRANT)

GRANT UPDATE, SELECT, DELETE, INSERT on raamatuBroneeri to Juht;

XAMPP:

Luuame andmebaas

LUUA DATABASE:

CREATE DATABASE raamatukogu;
USE raamatukogu;

LUUA TABLES:

CREATE TABLE raamatu(
raamatuID INT AUTO_INCREMENT PRIMARY KEY,
raamatuNimi VARCHAR(60),
raamatuHind INT);
insert into raamatu(raamatuNimi,raamatuHind)
Values ('Kampsun ja päike', 27);
insert into raamatu(raamatuNimi,raamatuHind)
Values ('Kaka ja kevad', 13);
insert into raamatu(raamatuNimi,raamatuHind)
Values ('Vanaema õunapuu otsas', 7);
 
 
CREATE TABLE raamatuBroneeri(
raamatuBroneeriID INT AUTO_INCREMENT PRIMARY KEY,
ostjaNimi VARCHAR(60),
ostjaPerenimi VARCHAR(60));
 
CREATE TABLE logi(
logiID INT PRIMARY KEY AUTO_INCREMENT,
kuupaev DATETIME,
sisestatudAndmed TEXT,
kasutaja VARCHAR(100));

CREATE TRIGGERS:

BEGIN
    INSERT INTO logi (kuupaev, sisestatudAndmed, kasutaja)
    VALUES (NOW(), CONCAT('Kustutatud andmed: ', OLD.ostjaNimi, ', ', OLD.ostjaPerenimi), USER());
END


BEGIN
    INSERT INTO logi (kuupaev, sisestatudAndmed, kasutaja)
    VALUES (NOW(), CONCAT('lisatud andmed ', NEW.ostjaNimi, ', ', NEW.ostjaPerenimi), USER());
END

BEGIN
    INSERT INTO logi (kuupaev, sisestatudAndmed, kasutaja)
    VALUES (NOW(), CONCAT('Кустутatud andmed: ', OLD.ostjaNimi, ', ', OLD.ostjaPerenimi,
    ' Uued andmed: ', NEW.ostjaNimi, ', ', NEW.ostjaPerenimi), USER());
END

Tegime seosed kahe tabeliga:

ALTER TABLE raamatuBroneeri ADD raamatuID int;
ALTER TABLE raamatuBroneeri ADD CONSTRAINT fk_raamatu
FOREIGN KEY (raamatuID) REFERENCES raamatu(raamatuID)

loome trigeri raamatuBroneeriLisamine:

BEGIN
    INSERT INTO logi (kuupaev, sisestatudAndmed, kasutaja)
    SELECT NOW(), 
           CONCAT('lisatud andmed ', NEW.ostjaNimi, ', ', NEW.ostjaPerenimi, ', ', raamatu.raamatuNimi), 
           USER()
    FROM raamatu
    WHERE raamatu.raamatuID = NEW.raamatuBroneeriID;
END

Kontrollime:

INSERT INTO raamatuBroneeri(ostjaNimi, ostjaPerenimi, raamatuID)
VALUES('Bogdan', 'Sats', 3);
Select * from logi;
select * from raamatuBroneeri;

loome trigeri raamatuBroneeriKustutamine:

BEGIN
    INSERT INTO logi (kuupaev, sisestatudAndmed, kasutaja)
    SELECT NOW(), 
           CONCAT('kustutatud andmed ', OLD.ostjaNimi, ', ', OLD.ostjaPerenimi, ', ', raamatu.raamatuNimi), 
           USER()
    FROM raamatu
    WHERE raamatu.raamatuID = OLD.raamatuBroneeriID;
END

kontrollimiseks kustutame raamatu tabelis raamatuBroneeri:

DELETE FROM raamatuBroneeri
WHERE raamatuBroneeriID=3;
SELECT * FROM raamatuBroneeri;
SELECT * FROM logi;

Loome trigeri raamatuBroneeriUuendamine:

BEGIN
    INSERT INTO logi (kuupaev, sisestatudAndmed, kasutaja)
    SELECT NOW(), 
           CONCAT(
               'vanad andmed - ', OLD.ostjaNimi, ', ', OLD.ostjaPerenimi, ', ', r1.raamatuNimi,
               ' uuendatud andmed - ', NEW.ostjaNimi, ', ', NEW.ostjaPerenimi, ', ', r2.raamatuNimi),
           USER()
    FROM raamatu r1 
    JOIN raamatu r2 ON r2.raamatuID = NEW.raamatuID
    WHERE r1.raamatuID = OLD.raamatuID;
END

kontrollimiseks uuendamine:

UPDATE raamatuBroneeri SET ostjaNimi='Ramas',ostjaPerenimi='Saar', raamatuID=3
WHERE raamatuBroneeriID=2;
 
SELECT * FROM raamatuBroneeri;
SELECT * FROM logi;

Loome User:

Kasutaja kontrollimine

Vsevolod Tsarev õpimapp | portfoolio

Noorem Tarkvaraarendaja eriala