SQL Server Triggers

Triggerite loomine

Ülesanne 1

  • Loo logitabel(id, toiming, aeg, autoAndmed), et registreerida muudatused autoregistris.
  • Mõelge ise välja väljad ja andmed autoregistri tabeli jaoks.
  • Looge trigerid andmete lisamise, kustutamise, muutmise registreerimiseks autoregistri tabelis, kusjuures andmed muudatuste kohta salvestatakse logitabel tabelisse.

SQL Server Managment Studio

1. Loome andmebaasi ja kaks tabeli.

-- Loo andmebaas
CREATE DATABASE AutoRegistr;

-- Kasuta loodud andmebaasi
USE AutoRegistr;

-- Loo tabel: autoregister
CREATE TABLE autoregister (
    AutoID INT IDENTITY(1,1) PRIMARY KEY,
    Brand VARCHAR(50) NOT NULL,
    Model VARCHAR(50) NOT NULL,
    Year INT,
    Color VARCHAR(50)
);

-- Loo tabel: logitabel
CREATE TABLE logitabel (
    id INT IDENTITY(1,1) PRIMARY KEY,
    Operation VARCHAR(100),
    Time DATETIME,
    [User] VARCHAR(25),
    AutoData TEXT
);

2. Loome Trigger-i andmete sisestamise fikseerimiseks tabelis LogTable.

CREATE TRIGGER trg_AutoRegister_insert
ON autoregister
FOR INSERT
AS    INSERT INTO logitabel (Operation, Time, [User], AutoData)
SELECT
        'INSERT',
        GETDATE(),
        SYSTEM_USER,
        'Brand: ' + Brand +
        ', Model: ' + Model +
        ', Year: ' + CAST(Year AS VARCHAR) +
        ', Color: ' + Color
    FROM inserted;

3. Andmete sisestamise fikseeriv Trigger-i testimine.

INSERT INTO autoregister (Brand, Model, Year, Color)
VALUES 
('Toyota', 'Corolla', 2020, 'Blue'),
('Toyota', 'Corolla', 2020, 'Blue'),
('Honda', 'Civic', 2019, 'Red'),
('Ford', 'Focus', 2018, 'Black');
SELECT * FROM logitabel WHERE Operation = 'INSERT';

4. Loome Trigger-i andmete kustutamise fikseerimiseks tabelis LogTable.

CREATE TRIGGER trg_AutoRegister_delete
ON autoregister
AFTER DELETE
AS
BEGIN
    INSERT INTO logitabel (Operation, Time, [User], AutoData)
    SELECT
        'DELETE',
        GETDATE(),
        SYSTEM_USER,
        'Brand: ' + d.Brand +
        ', Model: ' + d.Model +
        ', Year: ' + CAST(d.Year AS VARCHAR) +
        ', Color: ' + d.Color
    FROM deleted d;
END;

5. Andmete kustutamise fikseeriv Trigger-i testimine.

DELETE FROM autoregister WHERE Brand = 'Toyota' AND Model = 'Corolla';
SELECT * FROM logitabel WHERE Operation = 'DELETE';

6. Loome Trigger-i andmete uuendamise fikseerimiseks tabelis LogTable.

CREATE TRIGGER trg_AutoRegister_update
ON autoregister
FOR UPDATE
AS
INSERT INTO logitabel (Operation, Time, [User], AutoData)
SELECT
    'UPDATE',
    GETDATE(),
    SYSTEM_USER,
    'Vanad andmed - Brand: ' + d.Brand + ', Model: ' + d.Model +
    ', Year: ' + CAST(d.Year AS VARCHAR) + ', Color: ' + d.Color +
    CHAR(13) + CHAR(10) + -- line break
    'Uued andmed - Brand: ' + i.Brand + ', Model: ' + i.Model +
    ', Year: ' + CAST(i.Year AS VARCHAR) + ', Color: ' + i.Color
FROM deleted d
INNER JOIN inserted i ON d.AutoID = i.AutoID;

7. Andmete uuendamise fikseeriv Trigger-i testimine.

UPDATE autoregister SET Color = 'Red' WHERE AutoID = 5;
SELECT * FROM logitabel WHERE Operation = 'UPDATE';

XAMPP

1. Loome andmebaasi ja kaks tabeli.

-- Loo andmebaas
CREATE DATABASE AutoRegistr;
USE AutoRegistr;

-- Loo tabel autoregister 
CREATE TABLE autoregister(
AutoID int Auto_increment PRIMARY KEY,
Brand varchar(50) NOT NULL,
Model varchar(50) NOT NULL,
Year INT,
Color varchar(50)
);
 
-- Loo tabel logitabel  
CREATE TABLE logitabel(
id int Auto_increment PRIMARY KEY,
Operation  varchar(100),
Time DATETIME,
User varchar(25),
AutoData TEXT
);

2. Loome Trigger-i andmete sisestamise fikseerimiseks tabelis LogTable.

INSERT INTO logitabel(Operation, Time, User, AutoData)
SELECT
'INSERT',
NOW(),
USER(),
CONCAT('Brand: ', NEW.Brand, ', Model: ', NEW.Model, ', Year: ', NEW.Year,', Color: ', NEW.Color);

3. Lisame autod koos omanikega AutoRegister tabelisse

-- Lisa autod koos omanikega autoregister tabelisse
INSERT INTO autoregister (Brand, Model, Year, Color)
VALUES
('Toyota', 'Corolla', 2020, 'Blue'),
('Honda', 'Civic', 2019, 'Red'),
('Ford', 'Focus', 2018, 'Black');

4. Andmete sisestamise fikseeriv Trigger-i testimine.

-- Kontrolli, kas logitable'is on sisestatud logi "INSERT" tegevuse kohta
SELECT * FROM `logitabel` WHERE Operation = 'INSERT';

4. Loome Trigger-i andmete kustutamise fikseerimiseks tabelis LogTable.

INSERT INTO logitabel(Operation, Time, User, AutoData)
SELECT
'DELETE',
NOW(),
USER(),
CONCAT('Brand: ', OLD.Brand, ', Model: ', OLD.Model, ', Year: ', OLD.Year,', Color: ', OLD.Color)

4. Andmete kustutamise fikseeriv Trigger-i testimine.

DELETE FROM autoregister WHERE AutoID = 2;
SELECT * FROM `logitabel` WHERE Operation = 'DELETE';

5. Loome Trigger-i andmete uuendamise fikseerimiseks tabelis LogTable.

INSERT INTO logitabel (Operation, Time, User, AutoData)
    VALUES (
        'UPDATE',
        NOW(),
        USER(),
        CONCAT(
            'Vanad andmed - Brand: ', OLD.Brand, ', Model: ', OLD.Model, ', Year: ', OLD.Year, ', Color: ', OLD.Color,
            '\n\nUued andmed - Brand: ', NEW.Brand, ', Model: ', NEW.Model, ', Year: ', NEW.Year, ', Color: ', NEW.Color
        )
    );

6. Andmete uuendamise fikseeriv Trigger-i testimine.

UPDATE autoregister SET Color = 'Green' WHERE Brand = "Ford" and Model = "Focus";
SELECT * from logitabel WHERE Operation = 'UPDATE';

Ülesanne 2

  1. Lisage autoregistrisse mis tahes seotud tabel(Owners).
  1. Muutke uuenduste ja täienduste jälgimise trigerid nii, et logitabel tabelis kuvatakse seotud tabelite andmeid.
  2. Trigerid, mis jälgivad INSERT ja UPDATE toiminguid, põhinevad kahel seotud tabelil. Need trigerid salvestavad logitabelisse täiendatud aja, kasutajanime ning seotud tabelite vastavad andmed.

SQL Server Managment Studio

1. Loome omanike tabeli, lisame AutoRegister tabelisse viite omanikule ning seome need omavahel, et saaksime iga auto siduda konkreetse omanikuga.

-- Loo tabel: Omanikud
CREATE TABLE Owners ( 
    OwnersID INT PRIMARY KEY IDENTITY (1,1),
    OwnersName VARCHAR(50) NOT NULL,
    OwnersAddress VARCHAR(50),
    OwnersTel VARCHAR(20)
);

-- Lisa veerg autoregister tabelisse
ALTER TABLE autoregister 
ADD OwnersID INT;

-- Lisa välisvõti, mis seob autoregister.OwnersID -> Owners.OwnersID
ALTER TABLE autoregister 
ADD CONSTRAINT fk_Owners
FOREIGN KEY (OwnersID) REFERENCES Owners(OwnersID);

2. Uuendame andmete sisestamise fikseeriv Trigger.

ALTER TRIGGER trg_AutoRegister_insert
ON autoregister
AFTER INSERT
AS
BEGIN
    INSERT INTO logitabel (Operation, Time, [User], AutoData)
    SELECT
        'INSERT',
        GETDATE(),
        SUSER_NAME(),
        'Omanik: ' + o.OwnersName +
        ', Brand: ' + i.Brand +
        ', Model: ' + i.Model +
        ', Year: ' + CAST(i.Year AS VARCHAR) +
        ', Color: ' + i.Color
    FROM inserted i
    INNER JOIN Owners o ON o.OwnersID = i.OwnersID;
END;

3. Andmete sisestamise fikseeriv Trigger-i testimine.

INSERT INTO Owners (OwnersName, OwnersAddress, OwnersTel)
VALUES ('Karl Karu', 'Tartu, Puude tn 4', '51234567');
INSERT INTO autoregister (Brand, Model, Year, Color, OwnersID)
VALUES ('Audi', 'A6', 2021, 'Must', 1);
SELECT * FROM logitabel where Operation='INSERT';

4. Uuendame andmete kustutamise fikseeriv Trigger.

ALTER TRIGGER trg_AutoRegister_delete
ON autoregister
AFTER DELETE
AS
BEGIN
    INSERT INTO logitabel (Operation, Time, [User], AutoData)
    SELECT
        'DELETE',
        GETDATE(),
        SUSER_NAME(),
        'Omanik: ' + o.OwnersName +
        ', Brand: ' + d.Brand +
        ', Model: ' + d.Model +
        ', Year: ' + CAST(d.Year AS VARCHAR) +
        ', Color: ' + d.Color
    FROM deleted d
    INNER JOIN Owners o ON o.OwnersID = d.OwnersID;
END;

5. Andmete kustutamise fikseeriv Trigger-i testimine.

DELETE FROM autoregister WHERE Brand = 'Audi' AND Model = 'A6';
SELECT * FROM logitabel where Operation='DELETE';

6. Uuendame andmete uuendamise fikseeriv Trigger.

ALTER TRIGGER trg_AutoRegister_update 
ON autoregister
AFTER UPDATE
AS
BEGIN
    INSERT INTO logitabel (Operation, Time, [User], AutoData)
    SELECT
        'UPDATE',
        GETDATE(),
        SUSER_NAME(),
        'Vana omanik: ' + o1.OwnersName +
        CHAR(13) + CHAR(10) +
        'Vanad andmed – Brand: ' + d.Brand + ', Model: ' + d.Model +
        ', Year: ' + CAST(d.Year AS VARCHAR) + ', Color: ' + d.Color +
        CHAR(13) + CHAR(10) +
        'Uus omanik: ' + o2.OwnersName +
        CHAR(13) + CHAR(10) +
        'Uued andmed – Brand: ' + i.Brand + ', Model: ' + i.Model +
        ', Year: ' + CAST(i.Year AS VARCHAR) + ', Color: ' + i.Color
    FROM deleted d
    INNER JOIN inserted i ON d.AutoID = i.AutoID
    INNER JOIN Owners o1 ON o1.OwnersID = d.OwnersID
    INNER JOIN Owners o2 ON o2.OwnersID = i.OwnersID;
END;

7. Andmete uuendamise fikseeriv Trigger-i testimine.

UPDATE autoregister SET Color = 'Green' WHERE Brand = 'Ford' AND Model = 'Focus';
SELECT * FROM logitabel where Operation='UPDATE';

XAMPP

1. Loome omanike tabeli, lisame AutoRegister tabelisse viite omanikule ning seome need omavahel, et saaksime iga auto siduda konkreetse omanikuga.

-- Loo tabel: Owners
CREATE TABLE Owners ( 
    OwnersID INT AUTO_INCREMENT PRIMARY KEY,
    OwnersName VARCHAR(50) NOT NULL,
    OwnersAddress VARCHAR(50),
    OwnersTel VARCHAR(20)
);

-- Lisa veerg tabelisse autoregister
ALTER TABLE autoregister 
ADD COLUMN OwnersID INT;

-- Lisa välisvõti, mis seob autoregister.OwnersID -> Owners.OwnersID
ALTER TABLE autoregister 
ADD CONSTRAINT fk_Owners
FOREIGN KEY (OwnersID) REFERENCES Owners(OwnersID);

2. Uuendame andmete sisestamise fikseeriv Trigger.

Muudamiseks graafilises disainis

  1. Triggers–>Edit
  2. Sisesta kood “Definition” väljasse
INSERT INTO logitabel (Operation, Time, User, AutoData)
SELECT 
    'INSERT',
    NOW(),
    USER(),
    CONCAT('Omanik: ', o.OwnersName,
           ', Brand: ', a.Brand,
           ', Model: ', a.Model,
           ', Year: ', a.Year,
           ', Color: ', a.Color)
FROM (SELECT NEW.AutoID, NEW.Brand, NEW.Model, NEW.Year, NEW.Color, NEW.OwnersID) a
INNER JOIN Owners o ON a.OwnersID = o.OwnersID;

3. Andmete sisestamise fikseeriv Trigger-i testimine.

-- Lisa uus omanik
INSERT INTO Owners (OwnersName, OwnersAddress, OwnersTel)
VALUES ('Karl Karu', 'Tartu, Puude tn 4', '51234567');

-- Lisa uus auto registreering
INSERT INTO autoregister (Brand, Model, Year, Color, OwnersID)
VALUES ('Audi', 'A6', 2021, 'Must', 1);

-- Kontrolli logitabelit
SELECT * FROM logitabel WHERE Operation = 'INSERT';

4. Uuendame andmete kustutamise fikseeriv Trigger.

Muudamiseks graafilises disainis

  1. Triggers–>Edit
  2. Sisesta kood “Definition” väljasse
INSERT INTO logitabel (Operation, Time, User, AutoData)
SELECT 
    'DELETE',
    NOW(),
    USER(),
    CONCAT('Omanik: ', o.OwnersName,
           ', Brand: ', a.Brand,
           ', Model: ', a.Model,
           ', Year: ', a.Year,
           ', Color: ', a.Color)
FROM (SELECT OLD.AutoID, OLD.Brand, OLD.Model, OLD.Year, OLD.Color, OLD.OwnersID) a
INNER JOIN Owners o ON a.OwnersID = o.OwnersID

5. Andmete kustutamise fikseeriv Trigger-i testimine.

DELETE FROM autoregister WHERE AutoID = 4;
SELECT * FROM logitabel WHERE Operation = 'DELETE';

6. Uuendame andmete uuendamise fikseeriv Trigger.

Muudamiseks graafilises disainis

  1. Triggers–>Edit
  2. Sisesta kood “Definition” väljasse
INSERT INTO logitabel (Operation, Time, `User`, AutoData)
    SELECT
        'UPDATE',
        NOW(),
        USER(),
        CONCAT(
            'Vana omanik: ', o1.OwnersName, '\n',
            'Vanad andmed – Brand: ', OLD.Brand, ', Model: ', OLD.Model,
            ', Year: ', CAST(OLD.Year AS CHAR), ', Color: ', OLD.Color, '\n',
            'Uus omanik: ', o2.OwnersName, '\n',
            'Uued andmed – Brand: ', NEW.Brand, ', Model: ', NEW.Model,
            ', Year: ', CAST(NEW.Year AS CHAR), ', Color: ', NEW.Color
        )
    FROM autoregister a 
    	INNER JOIN Owners o1 ON o1.OwnersID = OLD.OwnersID  
    	INNER JOIN Owners o2 ON o2.OwnersID = NEW.OwnersID
    WHERE NEW.AutoID=a.AutoID

7. Andmete uuendamise fikseeriv Trigger-i testimine.

UPDATE autoregister SET Color = 'Black' WHERE AutoID = 6;
UPDATE autoregister SET Color = 'Green' WHERE AutoID = 4;
UPDATE autoregister SET Color = 'Orange' WHERE AutoID = 5;

SELECT * FROM logitabel WHERE Operation = 'UPDATE';

Ülesanne 3

  • Loo kasutaja, kes ei näe (DENY) logi tabelit ja ei saa trigerit muuta.

SQL Server Managment Studio

1. Lisame uus kasutaja

2. Anname õigused kasutajale

use AutoRegistr;
GRANT SELECT TO opilaneMLT;
 
-- DENY logi tabeli lugemise õigus
DENY SELECT, INSERT, UPDATE, DELETE ON logitabel TO opilaneMLT
 
-- DENY trigerite muutmise õigus
DENY ALTER ON OBJECT::dbo.autoregister TO opilaneMLT;

3. Kontrollime kasutaja õigused

Triggerid ei saa muuta. “Modify” on hall.

logitable ei ole nähtav Object Explorer-is ega koodi kaudu

Andmete sisestamine, kustutamine, uuendamine ja vaatamine

Admin kasutajalt kontrollime logitable

XAMPP

1. Lisame uus kasutaja

Privileges–>Add user account

2. Anname õigused kasutajale.

3. Kontrollime kasutaja õigused

LogTable ei ole nähtav

LogTable sissepääs on keeldud ja Triggerite vahekaart ei ole nähtav, seetõttu ei ole saadaval.

Kui proovime näidata Triggerid koodiga, siis tulemus on tühi.

SELECT * FROM autoregister;
INSERT INTO autoregister (Brand, Model, Year, Color, OwnersID) VALUES ('Volkswagen', 'Golf', 2022,'White', 1);
UPDATE autoregister set Year = 2025 where AutoID=2;
DELETE FROM autoregister WHERE AutoID=5;

Kontrollime root kasutajast logitabel