set xact_abort on
go

begin transaction
go

UPDATE dbo.AnagLav SET 
  DescrLav=N'Teflonatura'
  WHERE IdxLav=N'Fosf_Tefl'
UPDATE dbo.AnagLav SET 
  DescrLav=N'Sabbiatura'
  WHERE IdxLav=N'Sabb'
go

commit transaction
go

set xact_abort on;
go

begin transaction;
go

alter table LogStatoIstObj alter column
  CodStato nvarchar(50);
go

exec sp_rename 'PK_LogStatoIstObj_1', 'PK_LogStatoIstObj', 'object';
go

alter table LogStatoIstObj drop
  constraint FK_LogStatoIstObj_IstObj ;
go

alter table LogStatoIstObj add
  constraint FK_LogStatoIstObj_IstObj foreign key(IdxObj) references IstObj(IdxObj) on update cascade;
go

set ANSI_NULLS on;
go

alter TRIGGER trgStateUpd
ON IstObj
FOR UPDATE
AS
IF UPDATE (CodStato) 

DECLARE @dataMod DATETIME

SET @dataMod = GETDATE()

/* verifico che non sia già esistente il record (esempio cambio stato multiplo...) */
SELECT     l.IdxObj
FROM         LogStatoIstObj AS l INNER JOIN
                      inserted AS i ON l.IdxObj = i.IdxObj AND l.CodStato = i.CodStato
if(@@ROWCOUNT = 0)
	begin
		INSERT INTO LogStatoIstObj
							  (IdxObj, DataOra, Username, CodStato)
		SELECT   DISTINCT  i.IdxObj, @dataMod, i.UserMod, i.CodStato
		FROM         inserted AS i
	end
else
	begin
		UPDATE LogStatoIstObj
		SET DataOra = @dataMod
		FROM LogStatoIstObj AS l INNER JOIN inserted AS i ON l.IdxObj = i.IdxObj AND l.CodStato = i.CodStato
	end
go

commit;
go





-- registro versione...
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(133, GETDATE())
GO