Samuele Locatelli
81 lines
1.5 KiB
Transact-SQL
81 lines
1.5 KiB
Transact-SQL
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
|