MSSQL/Update View
< MSSQL
Zur Navigation springen
Zur Suche springen
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