select in trigger to view - err more than 1 value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

select in trigger to view - err more than 1 value

Author

Topic

JustJones
Starting Member

4 Posts

Posted - 2013-01-10 : 14:57:58

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-10 : 19:00:31

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-10 : 22:48:20

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/

wholesalenfljerseyssale
Starting Member

9 Posts

Posted - 2013-01-11 : 01:15:58

unspammed

JustJones
Starting Member

4 Posts

Posted - 2013-01-11 : 06:04:14

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 06:06:57

nope...last if doesnt make any sense

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

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

JustJones
Starting Member

4 Posts

Posted - 2013-01-11 : 07:05:14

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 09:18:04

where's the insert logic in current trigger? also where does those value come from?

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-01-11 : 13:10:49

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.

JustJones
Starting Member

4 Posts

Posted - 2013-01-11 : 17:35:36

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'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-12 : 04:05:33

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources