CREATE TABLE employee
(so_number char(11) not null
	primary key,
work_area varchar(30) null
)
CREATE TABLE service
(ev_code int
	primary key,
id_aircraft int not null
	references plane (id_aircraft),
so_number char(11) not null 
	references employee (so_number)
)
CREATE TABLE plane
(id_aircraft int not null
	primary key,  
han_id int not null references hangar (han_id)
)
CREATE TABLE hangar
(han_id int not null
	primary key,
location varchar(30) not null,
)

insert into hangar values 
('19','easter part','7','0'),
('11','western part','5','1'),
('12','southern part','6','1'),
('14','northern part','8','1')

insert into samolot values 
('123','FSS-123','19','15552'),
('452','MKN-452','12','14452'),
('113','BDG-123','14','19972'),
('872','LQH-872','19','15552'),
('820','NQG-820','14','14452'),
('012','AFG-012','11','19972'),
('044','NQG-044','12','19972')

insert into employee values 
('CA-243564-R','1100','eastern part','1','88062515488'),
('GF-876876-H','3300','eastern part','1','78022216872'),
('HW-253465-J','1800','southern part','2','78011615632'),
('SD-857206-W','2100','eastern part','1','76111513321'),
('BV-476789-X','3000','western part','2','79081546588')

insert into service values
('132122','2012-12-31','5','123','CA-243564-R'),
('468202','2012-04-12','8','123','GF-876876-H'),
('146444','2012-03-23','22','452','HW-253465-J'),
('187985','2012-06-12','1','872','SD-857206-W'),
('133564','2012-06-10','48','012','BV-476789-X')

create view workarea
as
	select e.so_number, e.work_area,
	h.location, p.id_aircraft from employee e 
	join service se on e.so_number = se.so_number
	join plane p on p.id_aircraft = se.id_aircraft
	join hangar h on h.han_id = p.han_id

declare @work_area varchar(40)=(select work_area from workarea where so_number = @so_number) --I take @so_number from inserted table and put it to this variable
declare @location varchar(40)=(select location from workarea where id_aircraft = @id_aircraft)--here the same, I declared id_aircraft in trigger and assigned select from inserted table

...
declare @workarea table
(
work_area varchar(100)
)
declare @location table
(
location varchar(100)
)

insert @work_area 
select w.work_area from workarea w
inner join inserted i
on i.so_number = w.so_number

insert @location 
select w.location 
from workarea w
inner join inserted i
on i.id_aircraft = w.id_aircraft

create trigger insert_to_service
on service
for insert
as
declare @so_number char(11) = (select so_number from inserted)
declare @id_aircraft int = (select id_aircraft from inserted)
declare @wa table
(
work_area varchar(100)
)
declare @lo table
(
location varchar(100)
)

insert into @wa
select w.work_area from workarea w
inner join inserted i
on i.so_number = w.so_number
insert into @lo
select w.location
from workarea w
inner join inserted i
on i.id_aircraft = w.id_aircraft
	if (select work_area from @wa) = (select location from @lo)
		print 'trigger allowed to do this operation !'
	else
	begin
		print 'trigger does not allowe to do this operation ! transaction will be rollback'
		rollback
	end

exec add_to_serwis '232435','2012-10-25','500','123','CA-243564-R'

('CA-243564-R','1100','eastern part','1','88062515488')

('123','FSS-123','19','15552')

('19','eastern part','7','0')

CREATE TABLE hangar
(han_id int not null
	primary key,
location varchar(30) not null,
)
CREATE TABLE plane
(id_aircraft int not null
	primary key,  
han_id int not null references hangar (han_id)
)
CREATE TABLE employee
(so_number char(13) not null
	primary key,
work_area varchar(30) null
)
CREATE TABLE service
(ev_code int
	primary key,
id_aircraft int not null
	references plane (id_aircraft),
so_number char(13) not null 
	references employee (so_number)
)


/*drop table plane
drop table hangar
drop table service
drop table employee*/

/*select * from hangar
select * from plane
select * from service
select * from employee*/

insert into hangar values 
('19','easter part'),
('11','western part'),
('12','southern part'),
('14','northern part')

insert into plane values 
('123','19'),
('452','12'),
('113','14'),
('820','14'),
('012','11'),
('044','12')

insert into employee values 
('CA-243564-R','eastern part'),
('GF-876876-H','eastern part'),
('HW-253465-J','southern part'),
('SD-857206-W','eastern part'),
('BV-476789-X','western part')

insert into service values
('132122','123','CA-243564-R'),
('146444','452','HW-253465-J'),
('187985','820','SD-857206-W'),
('133564','012','BV-476789-X')
go

create view workarea
as
	select e.so_number, e.work_area,
	h.location, p.id_aircraft from employee e 
	join service se on e.so_number = se.so_number
	join plane p on p.id_aircraft = se.id_aircraft
	join hangar h on h.han_id = p.han_id
go
/*select * from workarea
drop view workarea*/
go

create procedure insert_to_service
@ev_code int,
@id_aircraft int,
@so_number char(13)  
as
	begin tran
		insert into service values (@ev_code,@id_aircraft,@so_number)
		if @@error <> 0
			rollback
	commit tran
go

--drop procedure insert_to_service
go

create trigger check_loc
on service
for insert
as
	declare @so_number varchar(14)=(select so_number from inserted)
	declare @id_aircraft int=(select id_aircraft from inserted)
	declare @work_area varchar(40)=(select work_area from workarea where so_number = @so_number) 
	declare @location varchar(40)=(select location from workarea where id_aircraft = @id_aircraft)
	if (@work_area) = (@location)
		print 'hangar location and employee arok area are equal, trigger allowed to insert'
	else
	begin
		print 'hangar location and employee work area are not equal, trigger doesn not allowe to insert, transaction will be rolled back'
		rollback
	end
--drop trigger check_loc

exec insert_to_service '123456','123','HW-253465-J'

create trigger check_loc
on service
instead of insert
as
	
insert into service
select ev_code,id_aircraft,son_number
from inserted i
inner join workarea  w1
on w1.so_number = i.so_number
inner join workarea w2
on w2.id_aircraft = i.id_aircraft
where w1.work_area = w2.location

if exists (select 1
           from inserted i
           inner join workarea  w1
           on w1.so_number = i.so_number
           inner join workarea w2
           on w2.id_aircraft = i.id_aircraft
           where w1.work_area <> w2.location
        )
	begin
        	print 'hangar location and employee work area are not equal, trigger doesn not allowe to insert, transaction will be rolled back'
		rollback
	end