Cześć,
mam problem, muszę stworzyć procedury do tabel łączonych, do tej pory robiłam tylko procedury do jednej tabeli,
próbowałam utworzyć procedury na podstawie stworzonego widoku, ale nie idzie..
procedura delete, get i list udała się wykonać, natomiast update i insert wyskakuje komunikat:
"View or function 'EmployerV' is not updatable because the modification affects multiple base tables."
a oto co stworzyłam:
procedura update:
create procedure EmployerUpdate
@EmployerVID int, @LName varchar(50), @FName varchar(50), @PESEL char(9), @DepID int, @DepName varchar(50), @PosID int, @PosName varchar(50), @Salary decimal(18,2), @Bonus decimal(18,2)
as begin
update EmployerV
set
LName = @LName,
FName = @FName,
PESEL = @PESEL,
DepID = @DepID,
DepName = @DepName,
PosID = @PosID,
PosName = @PosName,
Salary = @Salary,
Bonus = @Bonus
where ID = @EmployerVID;
end;
procedura insert:
create procedure EmployerInsert
@EmployerVID int, @LName varchar(50), @FName varchar(50), @PESEL char(9), @DepID int, @DepName varchar(50), @PosID int, @PosName varchar(50), @Salary decimal(18,2), @Bonus decimal(18,2)
as begin
insert into EmployerV (LName, FName, PESEL, DepID, DepName, PosID, PosName, Salary, Bonus)
values (@LName, @FName, @PESEL, @DepID, @DepName, @PosID, @PosName, @Salary, @Bonus)
set @EmployerVID = @@IDENTITY
END
procedura delete:
create procedure EmployerDelete@EmployerVID int
as begin
delete from EmployerV
where ID = @EmployerVID;
end;
procedura get:
create procedure EmployerGet @EmployerVID int
as begin
select
ID, LName, FName, PESEL, DepID, PosID, Salary, Bonus, DepName, PosName
from EmployerV
where ID = @EmployerVID;
end;
procedura list:
CREATE PROCEDURE EmployerList
AS BEGIN
SELECT ID, LName, FName, PESEL, DepID, PosID, Salary, Bonus, DepName, PosName
from EmployerV;
end;
widok:
create view EmployerV
as select
Employer.ID,
Employer.LName,
Employer.FName,
Employer.PESEL,
Employer.DepID,
Employer.PosID,
Employer.Salary,
Employer.Bonus,
Department.Name as DepName,
Position.Name as PosName
from Employer
JOIN Department on Employer.DepID = DepID
join Position on Employer.PosID = PosID
tabele w załączniku
jak powinnam zrobić to prawidłowo? proszę o pomoc