Iseseisevtöö – trigerite ülesanne

Ülesanne 1

Trigerite ülesanne, mis on esitluses “trigerid_localhost.pptx” viimasel slaidil

▶ Loo logitabel(id, toiming, aeg, autoAndmed) autorite registeri muudatuste fikseerimiseks.
▶ Mõtle ise välja väljad ja andmed autorite registeri tabelile.
▶ Loo triggerid, et fikseerida andmete lisamist, kustutamist ja muutmist autorite registeri tabelis, samal ajal salvestades muudatuste andmed logitabelisse.

SQL Server

Andmebaasi ja tabelite loomine

INSERT INTO autoregister (mark, mudel, aasta)
VALUES 
    ('Audi', 'A7', '2013'),
    ('BMW', 'X5', '2016'),
    ('Toyota', 'Corolla', '2015'),
    ('Mercedes', 'S klass', '2019'),
    ('Mustang', 'GT', '1999');

Auto Lisamine

Kontrollime:

Auto Kustutamine

Kontrollime:

Auto uuendamine

CREATE TRIGGER autoUuendamine
ON autoregister
FOR UPDATE
AS
INSERT INTO logitabel(toiming, aeg, autoAndmed)
SELECT
'On tehtud UPDATE käsk',  --toiming
GETDATE(),  --aeg
CONCAT('Vanad andmed - Mark: ', deleted.mark, ', Mudel: ', deleted.mudel, ', Omanik: ', deleted.omanik,
' ; Uued andmed - Mark: ', inserted.mark, ', Mudel: ', inserted.mudel, ', Omanik: ', inserted.omanik)  --andmed
FROM deleted
INNER JOIN inserted
ON deleted.autoID=inserted.autoID;


UPDATE autoregister
SET mark = 'Porsche', mudel = '911', aasta = 2017
WHERE mark = 'Mustang' AND mudel = 'GT' AND aasta = 1999;
'Mustang', 'GT', '1999'

Kontrollime:

XAMPP

Andmebaasi ja tabelite loomine

INSERT INTO autoregister (mark, mudel, aasta)
VALUES 
    ('Audi', 'A7', 2013),
    ('BMW', 'X5', 2016),
    ('Toyota', 'Corolla', 2015),
    ('Mercedes', 'S klass', 2019),
    ('Mustang', 'GT', 1999);

Auto Lisamine

INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud INSERT käsk',
NOW(),
USER(),
CONCAT('Mark: ', NEW.mark, ', Mudel: ', NEW.mudel, ', Aasta: ', NEW.aasta);

Kontrollime:

Auto Kustutamine

INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE käsk',
NOW(),
USER(),
CONCAT('Mark: ', OLD.mark, ', Mudel: ', OLD.mudel, ', Aasta: ', OLD.aasta

Kontrollime:

Auto Uuendamine

INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud UPDATE käsk',
NOW(),
USER(),

CONCAT('Vanad andmed - Mark: ', OLD.mark, ', Mudel: ', OLD.mudel, ', Aasta: ', OLD.aasta,
'\n\nUued andmed - Mark: ', NEW.mark, ', Mudel: ', NEW.mudel, ', Aasta: ', NEW.aasta)
FROM autoregister a
INNER JOIN autoregister ar
ON a.autoID=ar.autoID
WHERE NEW.autoID=a.autoID;

Kontrollime:

Ülesanne 2

 1. Добавить к таблице autoregister любую связанную таблицу.
 2. Изменить триггеры на отслеживание обновлений и добавлений так, чтобы в таблице logitabel отображались данные из связанных таблиц.

SQL Server

CREATE TABLE omanikud (
    omanikID int PRIMARY KEY IDENTITY (1,1),
    nimi VARCHAR(50) NOT NULL,
    aadress VARCHAR(50),
    telefon VARCHAR(20)
);
 
ALTER TABLE autoregister ADD omanikID int;
ALTER TABLE autoregister ADD CONSTRAINT fk_omanikud
FOREIGN KEY (omanikID) References omanikud(omanikID)

INSERT INTO omanikud (nimi, aadress, telefon)
VALUES
('John Doe', '1234 Elm Street, Tallinn', '555-1234'),
('Jane Smith', '5678 Oak Avenue, Tartu', '555-5678'),
('Michael Johnson', '9101 Pine Road, Parnu', '555-9101'),
('Sarah Lee', '1122 Maple Lane, Viljandi', '555-1122'),
('Chris Evans', '3344 Birch Boulevard, Rakvere', '555-3344');

Auto Lisamine

Create trigger autoLisamine
ON autoregister
FOR INSERT
AS INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud INSERT',
GETDATE(),
SUSER_NAME(),
CONCAT('Omanik: ', o.nimi, ', Mark: ', inserted.mark, ', Mudel: ', inserted.mudel, ', Aasta: ', inserted.aasta)
FROM inserted
INNER JOIN omanikud o ON o.omanikID=inserted.omanikID;

Kontrollime:

Auto Kustutamine

Create trigger autoKustutamine
ON autoregister
FOR DELETE
AS INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE',
GETDATE(),
SUSER_NAME(),
CONCAT('Omanik: ', o.nimi, ', Mark: ', deleted.mark, ', Mudel: ', deleted.mudel, ', Aasta: ', deleted.aasta)
FROM deleted
INNER JOIN omanikud o ON o.omanikID=deleted.omanikID;

DELETE FROM autoregister WHERE mark = 'BMW' AND mudel = 'X5' AND aasta = 2016;

Kontrollime:

Auto Uuendamine

CREATE TRIGGER autoUuendamine
ON autoregister
FOR UPDATE
AS
INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud UPDATE',
GETDATE(),
SUSER_NAME(), 
CONCAT('Vanad andmed - Omanik: ', o1.nimi, ', Telefon: ', o1.telefon,
', Auto: ', deleted.mark, ', ', deleted.mudel, ', Aasta: ', deleted.aasta,
'/ Uued andmed - Omanik: ', o2.nimi, ', Telefon: ', o2.telefon,
', Auto: ', inserted.mark, ', ', inserted.mudel, ', Aasta: ', inserted.aasta)
FROM deleted
INNER JOIN inserted ON deleted.autoID=inserted.autoID
INNER JOIN omanikud o1 ON o1.omanikID=deleted.omanikID
INNER JOIN omanikud o2 ON o2.omanikID=inserted.omanikID;

UPDATE autoregister
SET mark = 'Mercedes', mudel = 'E-klass', aasta = 2021, omanikID = 3
WHERE mark = 'Mercedes' AND mudel = 'S klass' AND aasta = 2019;

Kontrollime:

XAMPP

CREATE TABLE omanikud (
omanikID int Auto_increment PRIMARY KEY,
nimi VARCHAR(50) NOT NULL,
aadress VARCHAR(50),
telefon VARCHAR(20)
);
 
ALTER TABLE autoregister ADD omanikID int;
ALTER TABLE autoregister ADD CONSTRAINT fk_omanikud
FOREIGN KEY (omanikID) References omanikud(omanikID)

Auto Lisamine

Kontrollime:

Auto Kustutamine

INSERT INTO logitabel(toiming, aeg, kasutajanimi, autoAndmed)
SELECT
'On tehtud DELETE käsk',
NOW(),
USER(),
CONCAT('Omanik: ', o.nimi, ', ', o.telefon,
              '; Auto: ', OLD.mark, ', ', OLD.mudel,
              ', Aasta: ', OLD.aasta)
FROM autoregister a
INNER JOIN omanikud o ON a.omanikID=o.omanikID
WHERE a.autoID=OLD.autoiD

Kontrollime:

Auto Uuendamine

Kontrollime: