MSSQL/Update View

Um eine Update-Able-View, die auf mehrere Tablellen schreiben soll, zu erstellen, ist ein Trigger notwendig:

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'T_FW_scd_persontable'

AND type = 'TR')

DROP TRIGGER T_FW_scd_persontable

GO



CREATE TRIGGER T_FW_scd_persontable ON FW_scd_persontable

INSTEAD OF UPDATE AS

BEGIN

Declare @ID int,@UpdateError nvarchar(255), @LastUpdate datetime, @SCD_GID nvarchar(8),@Email nvarchar(50),

@Phone nvarchar(50),@Firstname nvarchar(50),@LastName nvarchar(50), @Fax nvarchar(20), @Mobile nvarchar(20)



DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY STATIC

for SELECT [ID], [UpdateError], [LastUpdate],[SCD_GID],[Email],[Phone],[Firstname],[LastName],[Fax],[Mobile] FROM inserted



open cur

fetch next from cur into @ID,@UpdateError,@LastUpdate,@SCD_GID,@Email,@Phone,@FirstName,@LastName, @Fax,@Mobile



-- update editor-tbl

UPDATE editors SET Phone=@Phone,Short=@SCD_GID,Email=@Email,Fax =@Fax, Mobile=@Mobile,Firstname=@FirstName,LastName=@LastName WHERE ([editor_id] = @ID);

--

--update scd_update-tbl

UPDATE scd_update SET UpdateError=@UpdateError,LastUpdate=@LastUpdate WHERE ([id] = @ID);

END