Andmebaas - Triggers - Alex's Arsenal (Shop)

Logitabeli ja trigger’ite loomine autoriregistri muutuste logimiseks

Ülesanne:
Luua tabel logitabel(id, toiming, aeg, autoAndmed), mis fikseerib muudatused autoreregister tabelis. Samuti tuleb luua autoreregister tabel koos välja mõeldud veergudega ning teha triggerid, mis logivad INSERT, UPDATE ja DELETE toiminguid logitabel tabelisse. Kontrollida, kas triggerid töötavad.

🔹 1. Tabeli `autoreregister` loomine

Lõin tabeli, kuhu salvestatakse auto andmed. Väljad, mille valisin:

id – unikaalne ID (AUTO_INCREMENT)
reg_number – registreerimisnumber
owner_name – omaniku nimi
car_make – automark
car_model – automudel
year – tootmisaasta

CREATE TABLE autoreregister (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reg_number VARCHAR(20) NOT NULL UNIQUE,
    owner_name VARCHAR(100) NOT NULL,
    car_make VARCHAR(50) NOT NULL,
    car_model VARCHAR(50) NOT NULL,
    year INT NOT NULL
);

🔹 2. Tabeli `logitabel` loomine

See tabel salvestab kõik muudatused, mis toimuvad autoreregister tabelis. Väljad:

id – logikirje ID
toiming – INSERT, UPDATE või DELETE
aeg – muudatuse toimumise aeg (automaatselt)
autoAndmed – muudatuse kirjeldus tekstina

CREATE TABLE logitabel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    toiming VARCHAR(10) NOT NULL,
    aeg DATETIME DEFAULT CURRENT_TIMESTAMP,
    autoAndmed TEXT
);

🔹 3. Triggerite loomine

Lõin kolm triggerit, mis lisavad muudatuse logitabel tabelisse.

✅ INSERT Trigger

BEGIN
  INSERT INTO logitabel (toiming, autoAndmed)
  VALUES ('INSERT', CONCAT('Lisati auto: ', NEW.reg_number, ', ', NEW.owner_name, ', ', NEW.car_make, ', ', NEW.car_model, ', ', NEW.year));
END;

✅ UPDATE Trigger

CREATE TRIGGER log_update
AFTER UPDATE ON autoreregister
FOR EACH ROW
BEGIN
  INSERT INTO logitabel (toiming, autoAndmed)
  VALUES ('UPDATE', CONCAT('Muudeti auto: ', OLD.reg_number, ', uus omanik: ', NEW.owner_name));
END;

✅ DELETE Trigger

BEGIN
  INSERT INTO logitabel (toiming, autoAndmed)
  VALUES ('DELETE', CONCAT('Kustutati auto: ', OLD.reg_number, ', ', OLD.owner_name, ', ', OLD.car_make, ', ', OLD.car_model, ', ', OLD.year));
END;

🔹 4. Kontrollimine (Testimine)

Testisin, et kõik töötab korralikult:

Sisestamine:

INSERT INTO autoreregister (reg_number, owner_name, car_make, car_model, year)
VALUES ('EST123', 'Mari Maasikas', 'Toyota', 'Yaris', 2020);

2. Muutmine:

UPDATE autoreregister
SET owner_name = 'Jüri Õunapuu'
WHERE reg_number = 'EST123';

Kustutamine:

DELETE FROM autoreregister
WHERE reg_number = 'EST123';

Logi kontrollimine:

SELECT * FROM logitabel ORDER BY aeg DESC;

Lisasime seotud tabeli `owners` tabelile `autoreregister`, et hallata autode omanike andmeid.

Muudeti triggerite toimingud nii, et andmed tabelist owners kajastuvad ka logitabelis logitabel iga lisamise, uuendamise ja kustutamise korral autoreregister tabelis.

1. Lisasime seotud tabeli `owners`:

Tabel owners sisaldab auto omaniku andmeid, mis on seotud tabeliga autoreregister.

Tabeli `owners` loomine:

CREATE TABLE owners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact_info VARCHAR(255)
);

Tabeli `autoreregister` muutmine ja välisvõtme lisamine:

ALTER TABLE autoreregister
ADD COLUMN owner_id INT,
ADD CONSTRAINT fk_owner
FOREIGN KEY (owner_id) REFERENCES owners(id);

Triggerid:

[SQL Server] Ühe tabeli triggeri loomine harjutus

CREATE DATABASE autotools;
USE autotools;

CREATE TABLE owner (
    ownerID INT identity(1,1) PRIMARY KEY,
    ownerName VARCHAR(100),
    contactInfo VARCHAR(100)
);

CREATE TABLE carRegistration (
    carID INT identity(1,1) PRIMARY KEY,
    carName VARCHAR(50),
    year INT,
    licensePlate VARCHAR(10),
    ownerID INT,
    CONSTRAINT FK_owner FOREIGN KEY (ownerID) REFERENCES owner(ownerID)
);

CREATE TABLE actionLog (
    logID INT identity(1,1) PRIMARY KEY,
    timestamp DATETIME,
    action VARCHAR(100),
    carDetails TEXT,
    username VARCHAR(25)
);

Sisestan andmed:

INSERT INTO owner (ownerName, contactInfo) VALUES
('John Doe', '123-456-789'),
('Jane Smith', '987-654-321');


INSERT INTO carRegistration (carName, year, licensePlate, ownerID) VALUES
('Mitsubishi', 2008, '286MGE', 1),
('Toyota', 1996, '361MGW', 2);

Eemaldan andmed:

delete from autoregister where autoID=1

Uuendan andmed:

UPDATE carRegistration
SET licensePlate = '794RDT'
WHERE carID = 2;

Tehtud tegevused logi tabelis:

Triggerite loomine

-- Trigger for INSERT operation
CREATE TRIGGER afterCarInsert
ON carRegistration
AFTER INSERT
AS 
BEGIN
    INSERT INTO actionLog (timestamp, action, carDetails, username)
    SELECT 
        GETDATE(),
        'INSERT operation executed',
        CONCAT('Car Name: ', carRegistration.carName, 
               ', Year: ', carRegistration.year, 
               ', License Plate: ', carRegistration.licensePlate),
        SUSER_NAME()
    FROM carRegistration
    WHERE carID = (SELECT carID FROM inserted);
END;

-- Trigger for DELETE operation
CREATE TRIGGER afterCarDelete
ON carRegistration
AFTER DELETE
AS 
BEGIN
    INSERT INTO actionLog (timestamp, action, carDetails, username)
    SELECT 
        GETDATE(),
        'DELETE operation executed',
        CONCAT('Car Deleted: ', deleted.licensePlate),
        SUSER_NAME()
    FROM deleted;
END;

-- Trigger for UPDATE operation
CREATE TRIGGER afterCarUpdate
ON carRegistration
AFTER UPDATE
AS 
BEGIN
    INSERT INTO actionLog (timestamp, action, carDetails, username)
    SELECT 
        GETDATE(),
        'UPDATE operation executed',
        CONCAT('Old Data - Car Name: ', deleted.carName, ', Year: ', deleted.year, ', License Plate: ', deleted.licensePlate,
               ' | New Data - Car Name: ', inserted.carName, ', Year: ', inserted.year, ', License Plate: ', inserted.licensePlate),
        SUSER_NAME()
    FROM inserted
    INNER JOIN deleted ON inserted.carID = deleted.carID;
END;

[SQL Server] Harjutus kahe tabelite seosete (on loodud tabel 'omanik')

CREATE TABLE omanik (
    omanikID int identity(1,1) PRIMARY KEY,
    nimi varchar(50)
)

INSERT INTO omanik (nimi) VALUES
('Bob'), ('Oleg');

CREATE TRIGGER afterCarInsert
ON carRegistration
AFTER INSERT
AS 
BEGIN
    INSERT INTO actionLog (timestamp, action, carDetails, username)
    SELECT 
        GETDATE(),
        'INSERT operation executed',
        CONCAT('Car Name: ', carRegistration.carName, 
               ', Year: ', carRegistration.year, 
               ', License Plate: ', carRegistration.licensePlate,
               ', Owner Name: ', owner.nimi),
        SUSER_NAME()
    FROM carRegistration
    INNER JOIN owner ON carRegistration.ownerID = owner.ownerID
    INNER JOIN inserted ON carRegistration.carID = inserted.carID;
END;

CREATE TRIGGER afterCarUpdate
ON carRegistration
AFTER UPDATE
AS 
BEGIN
    INSERT INTO actionLog (timestamp, action, carDetails, username)
    SELECT 
        GETDATE(),
        'UPDATE operation executed',
        CONCAT('Old Data - Car Name: ', deleted.carName, ', Year: ', deleted.year, ', License Plate: ', deleted.licensePlate,
               ' | New Data - Car Name: ', inserted.carName, ', Year: ', inserted.year, ', License Plate: ', inserted.licensePlate),
        SUSER_NAME()
    FROM inserted
    INNER JOIN deleted ON inserted.carID = deleted.carID;
END;

CREATE TRIGGER afterCarDelete
ON carRegistration
AFTER DELETE
AS 
BEGIN
    INSERT INTO actionLog (timestamp, action, carDetails, username)
    SELECT 
        GETDATE(),
        'DELETE operation executed',
        CONCAT('Car Deleted: ', deleted.licensePlate),
        SUSER_NAME()
    FROM deleted;
END;

Andmebaas – Triggers

🔹 1. Tabeli autoreregister loomine

🔹 2. Tabeli logitabel loomine