13

use master
go
if exists(select * from sys.databases where name ='dbmoe')
    drop database dbmoe
go
create database dbmoe
go
use dbmoe
go
create table Encrip_ganr
(
 id int identity(1,1) PRimary key not null,
 name_ganr nvarchar(20)
)
create table Encrip_film
(
 id int identity(1,1) PRimary key not null,
 name_film nvarchar(20) not null,
 dist int,
 kod_ganr int not null,
 anotaciya nvarchar(10),
 foreign key (kod_ganr) references Encrip_ganr(id),
)
create table Encrip_chanel
(
 id int identity(1,1) PRimary key not null,
 name_film nvarchar(20)
)
create table Main
(
 id int identity(1,1) PRimary key not null,
 kod_film int not Null,
 kod_chanel int not null,
 time_begin real check (dbo.f1(time_begin)='true'),
 time_end real,
 foreign key (kod_film) references Encrip_film(id),
 foreign key (kod_chanel) references Encrip_chanel(id),
 check (dbo.f2(dist,Main.time_begin,Main.time_end)='true')
)
go
create trigger Myinsert1
on Main
instead of insert
as
    declare @a int=0
    if(exists(select kod_film from inserted))
        set @a=@a+1
    if(exists(select kod_chanel from inserted))
        set @a=@a+1
    if @a=2
     insert into Main values(select * from inserted)
go
if OBJECT_ID('view1','view')
    drop view view1
go
create view view1
as
    select Encrip_film.name_film,Encrip_film.anotaciya from Main inner join Encrip_film on Main.kod_film=Encrip_film.id
    inner join Encrip_ganr on Encrip_film.kod_ganr=Encrip_ganr.id
    inner join Encrip_chanel on Encrip_chanel.id=Main.kod_chanel
go
--if OBJECT_ID('view1','view')
--  drop view view1
--create procedure proc1
--as
  create procedure proc1 @kod_f int,@kod_c,@time_b
go