Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- create proc mojaproc
- (
- @film_id int
- )
- as
- BEGIN
- SET NOCOUNT OFF;
- DECLARE @filmPostoji INT;
- DECLARE @filmIznajmljen INT;
- select @filmPostoji = COUNT(*) from film f
- where f.film_id = @film_id
- if (@filmPostoji = 0)
- BEGIN
- RAISERROR('Film sa zadatim id ' + @film_id + ' ne postoji!')
- END
- SELECT @filmIznajmljen = COUNT(*) FROM iznajmljivanje_stavke is
- WHERE is.film_id = @film_id
- if (@filmIznajmljen = 0)
- BEGIN
- RAISERROR('Film sa zadatim id ' + @film_id + ' niko nije iznajmio!')
- END
- select c.* from clan c
- inner join iznajmljivanje i on c.clan_id = i.clan_id
- inner join iznajmljivanje_stavke is on i.iznajmljivanje_id = is.iznajmljivanje_id and is.film_id = @film_id
- END
- go;
- create trigger mojtriger on iznajmljivanje_stavke
- after insert
- as
- BEGIN
- DECLARE @cena int = 90;
- DECLARE @film_count INT;
- select @film_count = count(*) from iznajmljivanje_stavke is
- where is.iznajmljivanje_id = inserted.iznajmljivanje_id
- if(@film_count <= 3)
- BEGIN
- set @cena = 100;
- END
- set @cena = @cena * @film_count;
- DECLARE @clan_id INT;
- select @clan_id = i.clan_id from iznajmljivanje i
- where i.iznajmljivanje_id = inserted.iznajmljivanje_id
- DECLARE @clan_film_count INT;
- select @clan_film_count = count(*) from iznajmljivanje i
- where @clan_id = i.clan_id
- if(@clan_film_count = 1)
- BEGIN
- set @cena = @cena * 0.5
- END
- UPDATE iznajmljivanje i set i.iznos = @cena where i.iznajmljivanje_id = inserted.iznajmljivanje_id
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement