Erik Gerega õpimapp

create database Kohvik
use Kohvik;

CREATE TABLE menu(
roogId int primary key identity(1,1),
roogiNimi varchar(50),
terav bit,
hind DECIMAL(3,2));

CREATE TABLE logi(
id int primary key identity(1,1),
kasutaja varchar (100),
kuupaev datetime,
sisestatudAndmed text)

CREATE TRIGGER menuLisamine
on menu --tabel, misjalgatakse
FOR INSERT
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('lisatud andmet: ', inserted.roogiNimi, ', ', inserted.terav, ', ', inserted.hind)
FROM inserted

CREATE TRIGGER menuKustutamine
on menu --tabel, misjalgatakse
FOR DELETE
AS
INSERT INTO logi(kasutaja, kuupaev, sisestatudAndmed)
SELECT
USER,
GETDATE(),
CONCAT('kustutatud andmet ', deleted.roogiNimi, ', ', deleted.terav, ', ', deleted.hind)
FROM deleted

grant select on menu to opilane;
grant delete on menu to opilane;
grant insert on menu to opilane;

create database Kohvik
use Kohvik;

CREATE TABLE menu(
roogId int primary key AUTO_INCREMENT,
roogiNimi varchar(50),
terav bit,
hind DECIMAL(3,2));

CREATE TABLE logi(
id int primary key AUTO_INCREMENT,
kasutaja varchar (100),
kuupaev datetime,
sisestatudAndmed text)

--Kontroll

delete from menu
WHERE roogID=1;

SELECT * FROM menu;
SELECT * FROM logi;

--Kontroll

UPDATE menu
SET roogiNimi = 'juustuburger', terav = 0, hind = 6
WHERE roogID = 1;

SELECT * FROM menu;
SELECT * FROM logi;

select * from menu;
SELECT * FROM logi;