Ticket (SQL SERVER) - Deniss Gorjunov ÕPIMAPP ET

Tabelite koostamine

Triggerite koostamine

Kontroll

Triggerite koostamine

Kontroll

Procedure koostamine

Kontroll

Procedure koostamine

Kontroll

Create procedure

Kontroll

Code

CREATE TRIGGER TicketLisamine
ON ticket
FOR INSERT
AS
INSERT INTO logi(kuupaev, perenimed,tel)
Select Getdate(), Concat('Perenime: ',inserted.perenimi), Concat('Tel: ',inserted.tel)
FROM inserted
--kontroll
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Martin','Nõmeste', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Deniss','Gorjunov', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Timur','Denisenko', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Sasha','ROgovskii', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Kirill','Nõmeste', '5232322',10.4,'2023-03-23')

SELECT * FROM ticket;
SELECT * FROM logi;

CREATE TRIGGER TicketKustatumine
ON ticket
FOR DELETE
AS
INSERT INTO logi(kuupaev, perenimed,tel)
Select Getdate(), Concat('Perenime: ',deleted.perenimi), Concat('Tel: ',deleted.tel)
FROM deleted
--kontroll
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Martin','Nõmeste', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Deniss','Gorjunov', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Timur','Denisenko', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Sasha','ROgovskii', '5232322',10.4,'2023-03-23')
insert into ticket(nimi,perenimi, tel,hind,kuupaev)
values('Kirill','Nõmeste', '5232322',10.4,'2023-03-23')


Delete from ticket WHERE id>5;

CREATE PROCEDURE LisaTicket
@Fname varchar(100),
@Lname varchar(100),
@kuupaev_ date,
@tel_ varchar(20),
@hind_ float
AS 
Begin
INSERT INTO ticket(nimi, perenimi, kuupaev,tel,hind)
VALUES(@Fname, @Lname, @kuupaev_, @tel_, @hind_);
SELECT * FROM ticket;
SELECT * FROM logi;
END
---käivitamine
select * from ticket;
EXECUTE LisaTicket @Fname='Deniss',@Lname='Gorjunov', @kuupaev_='2032-04-23',@tel_='543543543',@hind_=23.4;
select * from ticket;
SELECT * FROM logi;

CREATE PROCEDURE KustaTicket
@id int
AS
BEGIN
SELECT * FROM ticket;
DELETE FROM ticket
Where @id=id;
SELECT * FROM ticket;
select * from logi;
END
--- käivitamine
exec KustaTicket @id=1;

create procedure kõigeArvTicket
AS
BEGIN
SELECT max(hind) FROM Ticket;
END;
--- käivitamine
Select * from Ticket;
exec kõigeArvTicket;

Ticket (XAMP)

Tabelite koostamine