Variable locale dans la fonction de triggersment T-SQL UPDATE ()

Je travaille sur un triggersur pour mettre à jour une table de notation de changement pour une table avec des faits de vente. Lorsque quelqu'un essaie de faire une mise à jour sur la table, il doit écrire une description puis la mise à jour est enregistrée (user, heure, table, champ, ID_Unique_de_la_table, ancienne valeur, nouvelle valeur)

J'essaie d'utiliser la fonction de triggersment UPDATE () pour la variable locale @fieldname. Le but est de comparer les valeurs supprimées / insérées pour les colonnes qui sont mises à jour plutôt que de parcourir chaque colonne. J'ai été capable d'utiliser la fonction UPDATE () lorsque j'indique explicitement le nom de la colonne, mais pas si je mets une variable au nom de la colonne que je veux vérifier.

J'apprécie vraiment tout conseil sur comment optimiser ce process! J'ai encore beaucoup à apprendre, et c'est ma première tentative de journalisation des changements.

Voici mon triggersur en entier:

• Ecriture d'un triggersur SQL Server – erreur
• Déclencheur SQL Server avec message d'erreur
• Création d'une table temporaire dans un TRIGGER avec GROUP BY
• Déclencheur SQL Server insérant des lignes de table entières, au lieu de nouvelles
• Sql CretedDate Trigger

ALTER TRIGGER [dbo].[TR_UpdateLog] ON [dbo].[Test_Update_Trigger] AFTER Update AS Declare @description nvarchar(1000) , @UserName nvarchar(128) , @oldValue nvarchar(255) , @newValue nvarchar(255) , @UniqueID nvarchar(255) , @fieldname nvarchar(128) , @oname NVARCHAR(100) , @OldSQL nvarchar(max) , @NewSQL nvarchar(max) , @i int , @c int , @numcolumns int , @numrows int; DECLARE @updated_table TABLE ( idx int Primary Key identity(1,1) , uniqueID nvarchar(255) NULL ) ; --Require the User to submit a description for the update Set @description = (SELECT * FROM [dbo].[Fact_Bookings_Audit_Description]) TRUNCATE TABLE [dbo].[Fact_Bookings_Audit_Description] IF @description is null BEGIN PRINT 'You must provide a description. Use the following text: TRUNCATE TABLE [dbo].[Fact_Bookings_Audit_Description] INSERT INTO [dbo].[Fact_Bookings_Audit_Description] SELECT "Your Text Here--use Single Quotes" Copy and paste above your query, type your description, and run the update again. '; ROLLBACK TRANSACTION; END --Set User and Table name Set @Username = system_User SET @oname = (SELECT OBJECT_NAME(parent_id) from sys.sortingggers where object_ID = @@PROCID) ------------------------------------------------------------- ---create tables of updated items to reference for old/new values INSERT INTO @updated_table SELECT distinct Table_ID from DELETED SELECT * INTO dbo.tempDelete FROM DEleted SELECT * INTO dbo.tempInsert FROM inserted -------------------------------------------------- --Set variables for loop through updated items Set @i = 1 Set @numrows = (SELECT count(*) from @updated_table) ------------------------------------------------------------ ---Set number of columns for loop through each field Set @numcolumns = (SELECT MAx(ordinal_position) FROM information_schema.columns where table_name = @oname ) --------------------------------------------------------------- --If there was an update IF @numrows > 0 BEGIN --loop through each individual updated row WHILE (@i <= (select max(idx) from @updated_table)) BEGIN --reset the column variable Set @c = 1 --loop through each column WHILE (@c <= @numcolumns) BEGIN Set @fieldname = (SELECT Column_name FROM information_schema.columns Where table_name = @oname AND ORDINAL_POSITION = @c) /**This is what I want to use-- I would only like to do the comparison on columns which were updated, not cycle through every column**/ --IF UPDATE(@fieldname) --BEGIN --set values for old and new values as well as unique ID for log table Set @UniqueID = (SELECT uniqueID from @updated_table u WHERE idx = @i) Set @OldSQL = 'SELECT @oldvalue = ' + @fieldname + ' from dbo.tempdelete d WHERE d.Table_ID = ' + @UniqueID; Set @NewSQL = 'SELECT @newvalue = ' + @fieldname + ' from dbo.tempInsert i WHERE i.Table_ID = ' + @UniqueID; EXEC sp_executesql @oldSQL, N'@oldvalue nvarchar(128) output', @oldValue = @oldValue output EXEC sp_executesql @newSQL, N'@newvalue nvarchar(128) output', @newValue = @newValue output ; --Insert into log table if value is changed IF isnull(@oldvalue,0) <> isnull(@newvalue,0) BEGIN INSERT INTO dbo.Fact_Bookings_ChangeAudit (Change_Date, Change_Time, [User], Table_Name, Field, Table_ID, Oldvalue, Newvalue, [Description]) VALUES(cast(datediff(DAY,0,getdate())as datetime),cast(getdate() as datetime),@UserName, @oname, @fieldname, @UniqueID, @oldValue, @newValue, @description); END --END --next column Set @c = @c + 1 END --next record Set @i = @i + 1 END END DROP TABLE dbo.tempDelete DROP table dbo.tempInsert GO 

• Suppression du slider t-sql
• Comment faire une vérification IF dans un triggersur SQL Server
• Comment empêchez-vous deux triggersurs sur des tables séparées des deux tirant en même time?
• Erreur lors de la suppression du triggersur
• MSSQL sortinggger on UPDATE: Prise en charge de plusieurs mises à jour de ligne

 with i as ( select case when updated(colA) then coalesce(cast(ColA as varchar(32)), 'null') end as newColA, case when updated(colB) then coalesce(cast(ColB as varchar(32)), 'null') end as newColB, ... from inserted ), d as ( select case when updated(colA) then coalesce(cast(ColA as varchar(32)), 'null') end as oldColA, case when updated(colB) then coalesce(cast(ColB as varchar(32)), 'null') end as oldColB, ... from deleted ), old as ( select c.Id, Col, Change from i unpivot (Change for Col in (newColA, newColB, ...)) where Change is not null ), new as ( select c.Id, Col, Change from d unpivot (Change for Col in (oldColA, oldColB, ...)) where Change is not null ) -- insert into Log select ... from old o inner join new n on n.Id = o.Id and n.Col = o.Col 

 with ... , new as (select 'i' as src ...), , old as (select 'd' as src ...), , combined as (select * from new union all select * from old) select Id, Col min(case when src = 'i' then Change end) as newVal, min(case when src = 'd' then Change end) as oldVal, from combined group by Id, Col;