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.
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 workareaas 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 linesdeclare @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 variabledeclare @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 |
|
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 winner join inserted ion i.so_number = w.so_numberinsert @location select w.location from workarea winner join inserted ion i.id_aircraft = w.id_aircraft ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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_serviceon servicefor insertasdeclare @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 @waselect w.work_area from workarea winner join inserted ion i.so_number = w.so_numberinsert into @loselect w.locationfrom workarea winner join inserted ion 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 sensecan you explain what you're trying to check there?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://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 194) 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 location5) 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 MVPhttp://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 sorryI 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 planedrop table hangardrop table servicedrop table employee*//*select * from hangarselect * from planeselect * from serviceselect * 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')gocreate view workareaas 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_idgo/*select * from workareadrop view workarea*/gocreate 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 trango--drop procedure insert_to_servicegocreate trigger check_locon servicefor insertas 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_locexec 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 thiscreate trigger check_locon serviceinstead of insertas insert into serviceselect ev_code,id_aircraft,son_numberfrom inserted iinner join workarea w1on w1.so_number = i.so_numberinner join workarea w2on w2.id_aircraft = i.id_aircraftwhere w1.work_area = w2.locationif 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|