procedury do tabel łączonych

0

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

1

W procedurze rób update/insert na tabeli Employer nie nawidoku, chyba ze chcesz to napisz triggera: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms175521(v=sql.105)

1 użytkowników online, w tym zalogowanych: 0, gości: 1