Vladislav Kudriašev Portfoolio

CREATE DATABASE TRIGGERKudriashevTARpv23;
USE TRIGGERKudriashevTARpv23;
--loome tabeli
CREATE TABLE toode(
toodeId int PRIMARY KEY identity(1,1),
toodeNimi varchar(50),
hind int);
--loome tabeli, mis täidab trigger
CREATE TABLE logi(
id int PRIMARY KEY identity(1,1),
kasutaja varchar(100),
kuupaev datetime,
sisestatudAndmed text);
-- INSERT TRIGGER - trigger, mis jälgib andmete lisamine tabelisse ja teeb vastava kirje tabelis logi
CREATE TRIGGER toodeLisamine
ON toode --tabel, mis jälgitakse
FOR INSERT
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('Lisatud andmed: ', inserted.toodenimi, ', ', inserted.hind)
FROM inserted;
--kontroll
--kontrollimiseks lisame toode
INSERT INTO toode(toodeNimi, hind)
VALUES ('magus õun', 10);
SELECT * FROM toode;
SELECT * FROM logi;
INSERT INTO toode(toodeNimi, hind)
VALUES ('ilus pirn', 30);
SELECT * FROM toode;
SELECT * FROM logi;
CREATE TRIGGER toodeKustutamine
ON toode --tabel, mis jälgitakse
FOR DELETE
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('Kustutatud andmed: ', deleted.toodenimi, ', ', deleted.hind)
FROM deleted;
--kontrollimiseks kustutame toode tabelis toode
DELETE FROM toode
WHERE toodeId=1;
SELECT * FROM toode;
SELECT * FROM logi;
CREATE TRIGGER toodeUuendamine
ON toode --tabel, mis jälgitakse
FOR UPDATE
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('Vanad andmed: ', deleted.toodenimi, ', ', deleted.hind, '; Uued andmed: ', inserted.toodenimi, ', ', inserted.hind)
FROM deleted INNER JOIN inserted
ON deleted.toodeId=inserted.toodeId;
--kontrollimiseks uuendame toode
SELECT * FROM toode;
UPDATE toode SET toodeNimi='orange melon'
WHERE toodeId=2;
SELECT * FROM toode;
SELECT * FROM logi;
CREATE TABLE categories (
	category_id INT IDENTITY (1, 1) PRIMARY KEY,
	category_name VARCHAR (255) NOT NULL
);
CREATE TABLE brands (
	brand_id INT IDENTITY (1, 1) PRIMARY KEY,
	brand_name VARCHAR (255) NOT NULL
);
CREATE TABLE products (
	product_id INT IDENTITY (1, 1) PRIMARY KEY,
	product_name VARCHAR (255) NOT NULL,
	brand_id INT NOT NULL,
	category_id INT NOT NULL,
	model_year SMALLINT NOT NULL,
	list_price DECIMAL (10, 2) NOT NULL,
	FOREIGN KEY (category_id) 
        REFERENCES categories (category_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (brand_id) 
        REFERENCES brands (brand_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE product_audits(
    change_id INT IDENTITY PRIMARY KEY,
    product_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    category_id INT NOT NULL,
    model_year SMALLINT NOT NULL,
    list_price DEC(10,2) NOT NULL,
    updated_at DATETIME NOT NULL,
    operation CHAR(3) NOT NULL,
    CHECK(operation = 'INS' or operation='DEL')
);
CREATE TRIGGER trg_product_audit
ON products
AFTER INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO product_audits(
        product_id, 
        product_name,
        brand_id,
        category_id,
        model_year,
        list_price, 
        updated_at, 
        operation
    )
    SELECT
        i.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        i.list_price,
        GETDATE(),
        'INS'
    FROM
        inserted i
    UNION ALL
    SELECT
        d.product_id,
        product_name,
        brand_id,
        category_id,
        model_year,
        d.list_price,
        GETDATE(),
        'DEL'
    FROM
        deleted d;
END
INSERT INTO categories (category_name) VALUES ('Electronics');
INSERT INTO brands (brand_name) VALUES ('TestBrand');
INSERT INTO products(
    product_name, 
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES (
    'Test product',
    1,
    1,
    2018,
    599
);
SELECT 
    * 
FROM 
    product_audits;

CREATE DATABASE Filmid;
USE Filmid
CREATE TABLE Filmid(
filimidID int PRIMARY KEY identity(1,1),
filmNimi varchar(50),
lavastaja varchar(50),
filmiKestus int);
CREATE TABLE logi(
logiID int PRIMARY KEY identity(1,1),
kuupaev datetime,
andmed text,
kasutaja varchar(50));
CREATE TRIGGER insertFilmid
ON Filmid
FOR INSERT
AS
INSERT INTO logi(kuupaev, andmed, kasutaja)
SELECT
GETDATE(),
CONCAT('Lisatud andmed: ', inserted.filmNimi, ', ', inserted.lavastaja, ', ', inserted.filimidID),
USER
FROM inserted;
CREATE TRIGGER deleteFilmid
ON Filmid
FOR DELETE
AS
INSERT INTO logi(kuupaev, andmed, kasutaja)
SELECT
GETDATE(),
CONCAT('Kustutanud andmed: ', deleted.filmNimi, ', ', deleted.lavastaja, ', ', deleted.filimidID),
USER
FROM deleted;
-- produtsent lisamine graafiliselt
GRANT INSERT, DELETE TO produtsent;
GRANT SELECT, INSERT, DELETE ON Filmid TO produtsent;
-- admin testid
INSERT INTO Filmid(filmNimi, lavastaja, filmiKestus)
VALUES ('test', 'test', 1234);
SELECT * FROM Filmid;
SELECT * FROM logi;
DELETE FROM Filmid WHERE filimidID=1;
SELECT * FROM Filmid;
SELECT * FROM logi;
-- kasutaja testid
INSERT INTO Filmid(filmNimi, lavastaja, filmiKestus)
VALUES ('test', 'test', 1234);
SELECT * FROM Filmid;
SELECT * FROM logi;
DELETE FROM Filmid WHERE filimidID=1;
SELECT * FROM Filmid;
SELECT * FROM logi;
-- kasutaja testid
USE Filmid;
INSERT INTO Filmid(filmNimi, lavastaja, filmiKestus)
VALUES ('test', 'test', 1234);
SELECT * FROM Filmid;
SELECT * FROM logi;
DELETE FROM Filmid WHERE filimidID=3;
SELECT * FROM Filmid;
SELECT * FROM logi;

CREATE TABLE Filmid(
filimidID int PRIMARY KEY AUTO_INCREMENT,
filmNimi varchar(50),
lavastaja varchar(50),
filmiKestus int);

CREATE TABLE logi(
logiID int PRIMARY KEY AUTO_INCREMENT,
kuupaev datetime,
andmed text,
kasutaja varchar(50));

INSERT INTO logi(kuupaev, andmed, kasutaja)
SELECT
NOW(),
CONCAT('Lisatud andmed: ', new.filmNimi, ', ', new.lavastaja, ', ', new.filimidID),
USER()

INSERT INTO logi(kuupaev, andmed, kasutaja)
SELECT
NOW(),
CONCAT('Kustutanud andmed: ', old.filmNimi, ', ', old.lavastaja, ', ', old.filimidID),
USER()

INSERT INTO Filmid(filmNimi, lavastaja, filmiKestus)
VALUES ('test', 'test', 1234);
SELECT * FROM Filmid;
SELECT * FROM logi;

DELETE FROM Filmid WHERE filimidID=1;
SELECT * FROM Filmid;
SELECT * FROM logi;