SQL/XAMPP Trigerid

create database trigerTARpv23;
use trigerTARpv23;

--loome tabeli toode
create table toode(
toodeID int primary key identity(1,1),
toodeNimi varchar(50),
hind int);

--loome tabeli, mis täitab triger
create table logi(
id int primary key identity(1,1),
kasutaja varchar(100),
kuupaev datetime,
sisestatudAndmed text);

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

--kotroll
--kontrolimiseks lisame toode
insert into toode(toodeNimi, hind)
values ('ilus pirn', 30);
select * from toode;
select * from logi;

-- delete trigger
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

--kontroll
delete from toode
where toodeID = 1;
select * from toode;
select * from logi;

--update trigger
create trigger toodeUuendamine
on toode --tabel, mis jälgitakse
for update
as
insert into logi(kasutaja, kuupaev,sisestatudAndmed)
select 
user,
getdate(),
CONCAT('Vana andmed: ', deleted.toodeNimi, ', ',deleted.hind, '. Uued admed: ', inserted.toodeNimi, ', ', inserted.hind)
from deleted inner join inserted
on deleted.toodeID=inserted.toodeID

--kontroll
select * from toode;
update toode set toodeNimi='orange melon'
where toodeID=2;
select * from toode;
select * from logi;

--tabel products loomine
create table products(
product_id int primary key identity(1,1),
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
);

--tabel product_audits(logi) loomine
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')
);

--triggeri loomine INSERT ja DELETE koos
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

--andmete lisamine
INSERT INTO products(
    product_name,
    brand_id, 
    category_id, 
    model_year, 
    list_price
)
VALUES (
    'Test product',
    1,
    1,
    2018,
    599
);

--andmete kustutamine
SELECT 
    * 
FROM 
    product_audits;

DELETE FROM 
    products
WHERE 
    product_id = 1;

insert into logi(kasutaja, kuupaev,sisestatudAndmed)
VALUES(
user(),
NOW(),
CONCAT('lisatud andmed ', NEW.toodeNimi, ', ',NEW.hind))

insert into logi(kasutaja, kuupaev,sisestatudAndmed)
VALUES(
user(),
NOW(),
CONCAT('kustutatud andmed ', OLD.toodeNimi, ', ',OLD.hind))

insert into logi(kasutaja, kuupaev,sisestatudAndmed)
VALUES(
user(),
now(),
CONCAT('Vanad andmed: ', OLD.toodeNimi, ', ',OLD.hind, '. Uued admed: ', NEW.toodeNimi, ', ', NEW.hind))