SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 select in trigger to view - err more than 1 value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JustJones
Starting Member

4 Posts

Posted - 01/10/2013 :  14:57:58  Show Profile  Reply with Quote
Hi everyone,
I have 4 tables:
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,
)


After that I inserted:

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')


After that I wrote view:

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


And I would like to use it in trigger but this lines
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

cause an error and I gets a message: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
Could someone help me with that ?

What should i change ?

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 01/10/2013 :  19:00:31  Show Profile  Reply with Quote
It's hard to offer any suggestion as you haven't told us what you are trying to do. I'd question why you are even writing a trigger. But, that aside, you need to write your tigger in such a way that it can handle multiple rows. Here is a link to an article that talk about just that:
http://www.brentozar.com/archive/2009/01/triggers-need-to-handle-multiple-records/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/10/2013 :  22:48:20  Show Profile  Reply with Quote
the way you've written logic will work correctly only for single row operations. the error clearly suggests that there was a batch operation.

To handle this you need to write logic like

...
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




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

wholesalenfljerseyssale
Starting Member

9 Posts

Posted - 01/11/2013 :  01:15:58  Show Profile  Reply with Quote
unspammed
Go to Top of Page

JustJones
Starting Member

4 Posts

Posted - 01/11/2013 :  06:04:14  Show Profile  Reply with Quote
first of all thank you guys for answers I appreciate it
@Lamprey - i would like to check if the so_number (employee) have the same string in his work_area filed like aircraft hangar location to which employee will be assign

@visakh16 - So trigger like below should works, but it doesn't, am I right ?
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/11/2013 :  06:06:57  Show Profile  Reply with Quote
nope...last if doesnt make any sense

can you explain what you're trying to check there?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

JustJones
Starting Member

4 Posts

Posted - 01/11/2013 :  07:05:14  Show Profile  Reply with Quote
1) I want to insert into table service new row:
exec add_to_serwis '232435','2012-10-25','500','123','CA-243564-R'

2) I want to check this guy 'CA-243564-R' to which work area is assigned, i see in the employee table this row:
('CA-243564-R','1100','eastern part','1','88062515488')
so I know that he has assigned 'eastern part'
3) I want to check this plane '123' to which hangar is assigned, in the plane table i can see this row:
('123','FSS-123','19','15552')
so I know that this plane is assigned to aircraft hanagr number 19
4) Then I check this aircraft hangar so to do that i look at the hangar table ans there is this row:
('19','eastern part','7','0')
so know that this hangar has eastern part location
5) I want to check if these values (hanagr location and work are) are equal, and i can see that this guy has work area:eastern part and aircraft hangar location is also: easter part so if should check true otherwise false.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/11/2013 :  09:18:04  Show Profile  Reply with Quote
where's the insert logic in current trigger? also where does those value come from?



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 01/11/2013 :  13:10:49  Show Profile  Reply with Quote
Any chance yo can fix you script so that we can run it without errors? The tables seem ok, but they are out of order and the insert statements do not make any sense at all.

If we have sample data we might be able to write a query to help.
Go to Top of Page

JustJones
Starting Member

4 Posts

Posted - 01/11/2013 :  17:35:36  Show Profile  Reply with Quote
Ok guys thanks for patience :) and I'm sorry
I wrote it again, everything is in correct order with no errors besides my question error. After you run code try run last line, this is my question.

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'

Edited by - JustJones on 01/11/2013 17:38:33
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/12/2013 :  04:05:33  Show Profile  Reply with Quote
I think what you need is a instead of trigger like this

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000