--* Mp 07.07.2006 Naredi Trigger na tabele tsiPartner, tsiBankaPartner, tsiPartnerKOntakt in tsiPe --* Beleži spremembe v datoteko tfsTabelaLog --* Če beleženja ne želiš, izvedi le prve štiri DROP TRIGGER ukaze, ali naredi Triggerje ponovno tako, da bo --* prvi ukaz v vseh štirih Create-ih Return, npr.: --* CREATE TRIGGER tsiPartnerLog ON tsiPartner FOR UPDATE, DELETE, INSERT --* NOT FOR REPLICATION --* AS --* Return --* Declare ... --* ... IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tsiPartnerLog' AND type = 'TR') DROP TRIGGER tsiPartnerLog go IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tsiPeLog' AND type = 'TR') DROP TRIGGER tsiPeLog go IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tsiBankaPartnerLog' AND type = 'TR') DROP TRIGGER tsiBankaPartnerLog go IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tsiPartnerKontaktLog' AND type = 'TR') DROP TRIGGER tsiPartnerKontaktLog go CREATE TRIGGER tsiPartnerLog ON tsiPartner FOR UPDATE, DELETE, INSERT NOT FOR REPLICATION AS -- Mp 20.06.2006 Beleži spremembe tabele tsiPartner Declare @TableName varchar(50) Declare @PKName varchar(50) Declare @CurrentUser nVarChar(20) Declare @UpdateDate datetime Declare @Workstation Varchar(50) Declare @sAction VarChar(10) Select @TableName= 'tsiPartner' Select @PKName= 'Zaporedje' Select @CurrentUser = system_user Select @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(8), getdate(), 108) Select @Workstation = host_name() IF @@RowCount <> 1 Return SET NoCount On IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @sAction = 'UPDATE' ELSE IF EXISTS(SELECT * FROM inserted) SET @sAction = 'INSERT' ELSE SET @sAction = 'DELETE' If @sAction = 'INSERT' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerID', NULL, Inserted.PartnerID FROM INSERTED WHERE Inserted.PartnerID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Maticna', NULL, Inserted.Maticna FROM INSERTED WHERE Inserted.Maticna Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Zavezanec',NULL, Inserted.Zavezanec FROM INSERTED WHERE Inserted.Zavezanec Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerDavcnaStevilka',NULL, Inserted.PartnerDavcnaStevilka FROM INSERTED WHERE Inserted.PartnerDavcnaStevilka Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerStatus',NULL, Inserted.PartnerStatus FROM INSERTED WHERE Inserted.PartnerStatus Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Insert', @PKName, Inserted.Zaporedje, 'PartnerNaziv1',NULL, Inserted.PartnerNaziv1 FROM INSERTED WHERE Inserted.PartnerNaziv1 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerNaziv2',NULL, Inserted.PartnerNaziv2 FROM INSERTED WHERE Inserted.PartnerNaziv2 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerNaziv3',NULL, Inserted.PartnerNaziv3 FROM INSERTED WHERE Inserted.PartnerNaziv3 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerUlica',NULL, Inserted.PartnerUlica FROM INSERTED WHERE Inserted.PartnerUlica Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PostaID',NULL, Inserted.PostaID FROM INSERTED WHERE Inserted.PostaID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'RegijaID',NULL, Inserted.RegijaID FROM INSERTED WHERE Inserted.RegijaID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'DrzavaID',NULL, Inserted.DrzavaID FROM INSERTED WHERE Inserted.DrzavaID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'ObrestiID',NULL, Inserted.ObrestiID FROM INSERTED WHERE Inserted.ObrestiID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerDniValute',NULL, Inserted.PartnerDniValute FROM INSERTED WHERE Inserted.PartnerDniValute Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Obvestilo',NULL, Inserted.Obvestilo FROM INSERTED WHERE Inserted.Obvestilo Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Razmerje',NULL, Inserted.Razmerje FROM INSERTED WHERE Inserted.Razmerje Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'CrnaLista',NULL, Inserted.CrnaLista FROM INSERTED WHERE Inserted.CrnaLista Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Sektor',NULL, Inserted.Sektor FROM INSERTED WHERE Inserted.Sektor Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'KontoIDKup',NULL, Inserted.KontoIDKup FROM INSERTED WHERE Inserted.KontoIDKup Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'KontoIDDob',NULL, Inserted.KontoIDDob FROM INSERTED WHERE Inserted.KontoIDDob Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PreslPartnerID',NULL, Inserted.PreslPartnerID FROM INSERTED WHERE Inserted.PreslPartnerID Is Not Null End If @sAction='UPDATE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerID', Deleted.PartnerID, Inserted.PartnerID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerID, '')<>IsNull(Inserted.PartnerID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Maticna', Deleted.Maticna, Inserted.Maticna FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Maticna, '')<>IsNull(Inserted.Maticna, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Zavezanec', Deleted.Zavezanec, Inserted.Zavezanec FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Zavezanec, 0)<>IsNull(Inserted.Zavezanec, 0) INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerDavcnaStevilka', Deleted.PartnerDavcnaStevilka, Inserted.PartnerDavcnaStevilka FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerDavcnaStevilka, '')<>IsNull(Inserted.PartnerDavcnaStevilka, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerStatus', Deleted.PartnerStatus, Inserted.PartnerStatus FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerStatus, '')<>IsNull(Inserted.PartnerStatus, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Update', @PKName, Inserted.Zaporedje, 'PartnerNaziv1', Deleted.PartnerNaziv1, Inserted.PartnerNaziv1 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerNaziv1, '')<>IsNull(Inserted.PartnerNaziv1, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerNaziv2', Deleted.PartnerNaziv2, Inserted.PartnerNaziv2 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerNaziv2, '')<>IsNull(Inserted.PartnerNaziv2, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerNaziv3', Deleted.PartnerNaziv3, Inserted.PartnerNaziv3 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerNaziv3, '')<>IsNull(Inserted.PartnerNaziv3, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerUlica', Deleted.PartnerUlica, Inserted.PartnerUlica FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerUlica, '')<>IsNull(Inserted.PartnerUlica, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PostaID', Deleted.PostaID, Inserted.PostaID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PostaID, '')<>IsNull(Inserted.PostaID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'RegijaID', Deleted.RegijaID, Inserted.RegijaID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.RegijaID, '')<>IsNull(Inserted.RegijaID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'DrzavaID', Deleted.DrzavaID, Inserted.DrzavaID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.DrzavaID, '')<>IsNull(Inserted.DrzavaID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'ObrestiID', Deleted.ObrestiID, Inserted.ObrestiID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.ObrestiID, '')<>IsNull(Inserted.ObrestiID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerDniValute', Deleted.PartnerDniValute, Inserted.PartnerDniValute FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerDniValute, 0)<>IsNull(Inserted.PartnerDniValute, 0) INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Obvestilo', Deleted.Obvestilo, Inserted.Obvestilo FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Obvestilo, '')<>IsNull(Inserted.Obvestilo, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Razmerje', Deleted.Razmerje, Inserted.Razmerje FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Razmerje, 0)<>IsNull(Inserted.Razmerje, 0) INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'CrnaLista', Deleted.CrnaLista, Inserted.CrnaLista FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.CrnaLista, 0)<>IsNull(Inserted.CrnaLista, 0) INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Sektor', Deleted.Sektor, Inserted.Sektor FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Sektor, '')<>IsNull(Inserted.Sektor, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'KontoIDKup', Deleted.KontoIDKup, Inserted.KontoIDKup FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.KontoIDKup, '')<>IsNull(Inserted.KontoIDKup, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'KontoIDDob', Deleted.KontoIDDob, Inserted.KontoIDDob FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.KontoIDDob, '')<>IsNull(Inserted.KontoIDDob, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PreslPartnerID', Deleted.PreslPartnerID, Inserted.PreslPartnerID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PreslPartnerID, '')<>IsNull(Inserted.PreslPartnerID, '') End If @sAction = 'DELETE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerID', Deleted.PartnerID, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'Maticna', Deleted.Maticna, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'Zavezanec', Deleted.Zavezanec, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerDavcnaStevilka', Deleted.PartnerDavcnaStevilka, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerStatus', Deleted.PartnerStatus, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerNaziv1', Deleted.PartnerNaziv1, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerNaziv2', Deleted.PartnerNaziv2, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerNaziv3', Deleted.PartnerNaziv3, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerUlica', Deleted.PartnerUlica, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PostaID', Deleted.PostaID, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'RegijaID', Deleted.RegijaID, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'DrzavaID', Deleted.DrzavaID, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'ObrestiID', Deleted.ObrestiID, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PartnerDniValute', Deleted.PartnerDniValute, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'Obvestilo', Deleted.Obvestilo, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'Razmerje', Deleted.Razmerje, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'CrnaLista', Deleted.CrnaLista, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'Sektor', Deleted.Sektor, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'KontoIDKup', Deleted.KontoIDKup, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'KontoIDDob', Deleted.KontoIDDob, Null FROM DELETED INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Delete', @PKName, Deleted.Zaporedje, 'PreslPartnerID', Deleted.PreslPartnerID, Null FROM DELETED End go CREATE TRIGGER tsiBankaPartnerLog ON tsiBankaPartner FOR UPDATE, DELETE, INSERT NOT FOR REPLICATION AS -- Mp 20.06.2006 Beleži spremembe tabele tsiBankaPartner Declare @TableName varchar(50) Declare @PKName varchar(50) Declare @CurrentUser nVarChar(20) Declare @UpdateDate datetime Declare @Workstation Varchar(50) Declare @sAction VarChar(10) Select @TableName= 'tsiBankaPartner' Select @PKName= 'Zaporedje' Select @CurrentUser = system_user Select @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(8), getdate(), 108) Select @Workstation = host_name() IF @@RowCount <> 1 Return SET NoCount On IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @sAction = 'UPDATE' ELSE IF EXISTS(SELECT * FROM inserted) SET @sAction = 'INSERT' ELSE SET @sAction = 'DELETE' If @sAction = 'INSERT' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerID', NULL, Inserted.PartnerID FROM INSERTED WHERE Inserted.PartnerID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'BankaID', NULL, Inserted.BankaID FROM INSERTED WHERE Inserted.BankaID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerZR1', NULL, Inserted.PartnerZR1 FROM INSERTED WHERE Inserted.PartnerZR1 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerZR2', NULL, Inserted.PartnerZR2 FROM INSERTED WHERE Inserted.PartnerZR2 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerZR3', NULL, Inserted.PartnerZR3 FROM INSERTED WHERE Inserted.PartnerZR3 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'ZaporedjeZr', NULL, Inserted.ZaporedjeZr FROM INSERTED WHERE Inserted.ZaporedjeZr Is Not Null End If @sAction='UPDATE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerID', Deleted.PartnerID, Inserted.PartnerID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerID, '')<>IsNull(Inserted.PartnerID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Update', @PKName, Inserted.Zaporedje, 'BankaID', Deleted.BankaID, Inserted.BankaID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.BankaID, '')<>IsNull(Inserted.BankaID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerZR1', Deleted.PartnerZR1, Inserted.PartnerZR1 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerZR1, '')<>IsNull(Inserted.PartnerZR1, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerZR2', Deleted.PartnerZR2, Inserted.PartnerZR2 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerZR2, '')<>IsNull(Inserted.PartnerZR2, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerZR3', Deleted.PartnerZR3, Inserted.PartnerZR3 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerZR3, '')<>IsNull(Inserted.PartnerZR3, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'ZaporedjeZr', Deleted.ZaporedjeZr, Inserted.ZaporedjeZr FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.ZaporedjeZr, '')<>IsNull(Inserted.ZaporedjeZr, '') End If @sAction = 'DELETE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PartnerID', Deleted.PartnerID, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'BankaID', Deleted.BankaID, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PartnerZR1', Deleted.PartnerZR1, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PartnerZR2', Deleted.PartnerZR2, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PartnerZR3', Deleted.PartnerZR3, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'ZaporedjeZr', Deleted.ZaporedjeZr, Null FROM Deleted End go CREATE TRIGGER tsiPartnerKontaktLog ON tsiPartnerKontakt FOR UPDATE, DELETE, INSERT NOT FOR REPLICATION AS -- Mp 20.06.2006 Beleži spremembe tabele tsiPartnerKontakt Declare @TableName varchar(50) Declare @PKName varchar(50) Declare @CurrentUser nVarChar(20) Declare @UpdateDate datetime Declare @Workstation Varchar(50) Declare @sAction VarChar(10) Select @TableName= 'tsiPartnerKontakt' Select @PKName= 'Zaporedje' Select @CurrentUser = system_user Select @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(8), getdate(), 108) Select @Workstation = host_name() IF @@RowCount <> 1 Return SET NoCount On IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @sAction = 'UPDATE' ELSE IF EXISTS(SELECT * FROM inserted) SET @sAction = 'INSERT' ELSE SET @sAction = 'DELETE' If @sAction = 'INSERT' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerID', NULL, Inserted.PartnerID FROM INSERTED WHERE Inserted.PartnerID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Kontakt', NULL, Inserted.Kontakt FROM INSERTED WHERE Inserted.Kontakt Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Telefon', NULL, Inserted.Telefon FROM INSERTED WHERE Inserted.Telefon Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'Fax', NULL, Inserted.Fax FROM INSERTED WHERE Inserted.Fax Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'eMail', NULL, Inserted.eMail FROM INSERTED WHERE Inserted.eMail Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'AliOdprte', NULL, Inserted.AliOdprte FROM INSERTED WHERE Inserted.AliOdprte Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'VrstniRed', NULL, Inserted.VrstniRed FROM INSERTED WHERE Inserted.VrstniRed Is Not Null End If @sAction='UPDATE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerID', Deleted.PartnerID, Inserted.PartnerID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerID, '')<>IsNull(Inserted.PartnerID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName, 'Update', @PKName, Inserted.Zaporedje, 'Kontakt', Deleted.Kontakt, Inserted.Kontakt FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Kontakt, '')<>IsNull(Inserted.Kontakt, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Telefon', Deleted.Telefon, Inserted.Telefon FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Telefon, '')<>IsNull(Inserted.Telefon, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Fax', Deleted.Fax, Inserted.Fax FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Fax, '')<>IsNull(Inserted.Fax, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'Email', Deleted.Email, Inserted.Email FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.Email, '')<>IsNull(Inserted.Email, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'AliOdprte', Deleted.AliOdprte, Inserted.AliOdprte FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.AliOdprte, '')<>IsNull(Inserted.AliOdprte, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'VrstniRed', Deleted.VrstniRed, Inserted.VrstniRed FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.VrstniRed, '')<>IsNull(Inserted.VrstniRed, '') End If @sAction = 'DELETE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PartnerID', Deleted.PartnerID, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'Kontakt', Deleted.Kontakt, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'Telefon', Deleted.Telefon, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'Fax', Deleted.Fax, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'Email', Deleted.Email, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'AliOdprte', Deleted.AliOdprte, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'VrstniRed', Deleted.VrstniRed, Null FROM Deleted End go CREATE TRIGGER tsiPeLog ON tsiPe FOR UPDATE, DELETE, INSERT NOT FOR REPLICATION AS -- Mp 20.06.2006 Beleži spremembe tabele tsiPartnerPe Declare @TableName varchar(50) Declare @PKName varchar(50) Declare @CurrentUser nVarChar(20) Declare @UpdateDate datetime Declare @Workstation Varchar(50) Declare @sAction VarChar(10) Select @TableName= 'tsiPe' Select @PKName= 'Zaporedje' Select @CurrentUser = system_user Select @UpdateDate = convert(varchar(8), getdate(), 112) + ' ' + convert(varchar(8), getdate(), 108) Select @Workstation = host_name() IF @@RowCount <> 1 Return SET NoCount On IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted) SET @sAction = 'UPDATE' ELSE IF EXISTS(SELECT * FROM inserted) SET @sAction = 'INSERT' ELSE SET @sAction = 'DELETE' If @sAction = 'INSERT' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PartnerID', NULL, Inserted.PartnerID FROM INSERTED WHERE Inserted.PartnerID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PeID', NULL, Inserted.PeID FROM INSERTED WHERE Inserted.PeID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PeNaziv1', NULL, Inserted.PeNaziv1 FROM INSERTED WHERE Inserted.PeNaziv1 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PeNaziv2', NULL, Inserted.PeNaziv2 FROM INSERTED WHERE Inserted.PeNaziv2 Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PeUlica', NULL, Inserted.PeUlica FROM INSERTED WHERE Inserted.PeUlica Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PostaID', NULL, Inserted.PostaID FROM INSERTED WHERE Inserted.PostaID Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PeKontakt', NULL, Inserted.PeKontakt FROM INSERTED WHERE Inserted.PeKontakt Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'PeTel', NULL, Inserted.PeTel FROM INSERTED WHERE Inserted.PeTel Is Not Null INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Insert', @PKName, Inserted.Zaporedje, 'EanKoda', NULL, Inserted.EanKoda FROM INSERTED WHERE Inserted.EanKoda Is Not Null End If @sAction='UPDATE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PartnerID', Deleted.PartnerID, Inserted.PartnerID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PartnerID, '')<>IsNull(Inserted.PartnerID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PeID', Deleted.PeID, Inserted.PeID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PeID, '')<>IsNull(Inserted.PeID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PeNaziv1', Deleted.PeNaziv1, Inserted.PeNaziv1 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PeNaziv1, '')<>IsNull(Inserted.PeNaziv1, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PeNaziv2', Deleted.PeNaziv2, Inserted.PeNaziv2 FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PeNaziv2, '')<>IsNull(Inserted.PeNaziv2, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PeUlica', Deleted.PeUlica, Inserted.PeUlica FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PeUlica, '')<>IsNull(Inserted.PeUlica, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PostaID', Deleted.PostaID, Inserted.PostaID FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PostaID, '')<>IsNull(Inserted.PostaID, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PeKontakt', Deleted.PeKontakt, Inserted.PeKontakt FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PeKontakt, '')<>IsNull(Inserted.PeKontakt, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'PeTel', Deleted.PeTel, Inserted.PeTel FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.PeTel, '')<>IsNull(Inserted.PeTel, '') INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Update', @PKName, Inserted.Zaporedje, 'EanKoda', Deleted.EanKoda, Inserted.EanKoda FROM INSERTED INNER JOIN DELETED ON INSERTED.Zaporedje=DELETED.Zaporedje WHERE IsNull(Deleted.EanKoda, '')<>IsNull(Inserted.EanKoda, '') End If @sAction = 'DELETE' Begin INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PartnerID', Deleted.PartnerID, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PeID', Deleted.PeID, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PeNaziv1', Deleted.PeNaziv1, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PeNaziv2', Deleted.PeNaziv2, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PeUlica', Deleted.PeUlica, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PostaID', Deleted.PostaID, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PeKontakt', Deleted.PeKontakt, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'PeTel', Deleted.PeTel, Null FROM Deleted INSERT INTO tfsTabelaLog (recDateTime, WorkstationID, CurrentUser, TableName, TableAction, recIDField, recID, FieldName, OldValue, NewValue)SELECT @UpdateDate, @Workstation, @CurrentUser, @TableName,'Delete', @PKName, Deleted.Zaporedje, 'EanKoda', Deleted.EanKoda, Null FROM Deleted End