CREATE TABLE guest (
id int primary key auto_increment,
first_name varchar(80),
last_name varchar(80) NULL,
member_since date
);
Select * from guest;

CREATE TABLE logi(
    id int PRIMARY KEY AUTO_INCREMENT,
    kuupaev datetime,
    kasutaja varchar(100),
	andmed TEXT,
	tegevus varchar(100))

INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), USER(), 
        Concat(NEW.last_name, ', ', NEW.first_name, ', ', NEW.member_since),
        'guest on lisatud')

INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), USER(), 
        Concat(OLD.last_name, ', ', OLD.first_name, ', ', OLD.member_since),
        'guest on kustutatud')

DELETE FROM guest 
WHERE id=1;
SELECT * FROM guest;
SELECT * FROM logi;

INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), USER(), 
        Concat('Vanad andmed: ',OLD.last_name, ', ',
OLD.first_name, ', ', OLD.member_since,
              '\n Uued andmed: ', NEW.last_name, ', ', 
NEW.first_name, ', ', NEW.member_since),
        'guest on lisatud')

UPDATE guest SET first_name = "Luca"
WHERE id=2;
SELECT * FROM guest;
SELECT * FROM logi;

CREATE TABLE room (
id int primary key AUTO_INCREMENT,
number varchar(10),
name varchar(40),
status varchar(10),
smoke bit
);
Select * from room;

INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), USER(), 
        Concat(NEW.number, ', ', NEW.name, ', ', NEW.status, ', ', NEW.smoke),
        'room on lisatud')
       

INSERT INTO logi(kuupaev, kasutaja, andmed, tegevus)
VALUES (NOW(), USER(), 
        Concat(OLD.number, ', ', OLD.name, ', ', OLD.status, ', ', OLD.smoke),
        'room on kustutatud')

insert into logi(kuupaev,kasutaja,andmed,tegevus)
values(now(),user(),     
       concat('vanad andmed: ',old.number,', ',old.name,', ',old.status,', ',old.smoke, 'uued andmed: ',new.number,', ',new.name,', ',new.status,', ',new.smoke),       'room on uuendatud')

CREATE TABLE guest (
id int primary key identity(1,1),
first_name varchar(80),
last_name varchar(80) NULL,
member_since date
);
Select * from guest;


CREATE TABLE logi(
    id int PRIMARY KEY identity(1,1),
    kuupaev datetime,
    kasutaja varchar(100),
	andmed TEXT,
	tegevus varchar(100))

--loo triger
create trigger guestLisamine
on guest
for insert
as
insert into logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user,
concat(inserted.first_name, ', ', inserted.last_name),
'guest on lisatud'
from inserted

--kontroll
insert into guest(first_name, last_name, member_since)
values ('Karl','Luih','2023-11-16');
select * from guest;
select * from logi;

--loo triger
create trigger guestKustutamine
on guest
for delete
as
insert into logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user,
concat(deleted.first_name, ', ', deleted.last_name),
'guest on kustutatud'
from deleted

--kontroll
delete from guest where id=1
select * from guest;
select * from logi;

--loo triger
create trigger guestUuendamine
on guest
for update
as
insert into logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user,
concat('Vanad - ',
deleted.first_name,
', ',
deleted.last_name,
'Uued - ',
inserted.first_name,
', ',
inserted.last_name),
'guest on uuendatud'
from deleted inner join inserted
on deleted.id=inserted.id

--kontroll
select * from guest;
update guest set first_name='Luca'
where id=4;
select * from guest;
select * from logi;

--loo triger
create trigger roomLisamine
on room
for insert
as
insert into logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user,
concat(inserted.number, ', ', inserted.name, ', ', inserted.status, ', ', inserted.smoke),
'guest on lisatud'
from inserted


--kontroll
insert into room(number, name, status, smoke)
values ('156','vip','free',1)
select * from room;
select * from logi;

--loo triger
create trigger roomKustutamine
on room
for delete
as
insert into logi(kuupaev, kasutaja, andmed, tegevus)
select getdate(), user,
concat(deleted.number, ', ', deleted.name, ', ', deleted.status, ', ', deleted.smoke),
'room on kustutamine'
from deleted


--kontroll
delete from room where id=1
select * from room;
select * from logi;

--protseduurid
--andmete lisamine ja select
--kustutamine ja select
--andmete uuendamine sisestatud id järgi
-- kasutajate (guest) arvu leidmine
--oma



create procedure lisaGuest
@fiName varchar(100),
@laName varchar(100),
@since date
as
begin
insert into guest(first_name, last_name, member_since)
values (@fiName, @laName, @since);
select * from guest;
select * from logi;
end;
-- käivitamine

exec lisaGuest 'Deniss', 'Anmer', '2022-10-6';

create procedure deleteGuest
@guid int
as
begin
select * from guest;
delete from guest
where @guid=id;
select * from guest;
select * from logi;
end;
-- käivitamine

exec deleteGuest 3;