Comment ajouter une colonne "Dernière modification" et "créée" dans une table SQL Server?

Je conçois un nouveau schéma db pour une base de données SQL Server 2012.

Chaque table devrait recevoir deux colonnes supplémentaires appelées modified et created qui devraient être automatiquement modifiées dès qu'une ligne est insérée ou mise à jour.

Je ne sais pas comment plutôt la meilleure façon d'y arriver.

Je suppose que trigger est la meilleure façon de le gérer.

J'essayais de trouver des exemples avec des déclencheurs.. mais les tutoriels que j'ai trouvés insèrent des données dans une autre table, etc.

J'ai supposé que c'était un scénario assez commun mais je n'ai pas encore trouvé la réponse.

23
demandé sur gotqn 2015-07-26 11:09:19

3 réponses

La colonne created est simple - juste une colonne DATETIME2(3) avec une contrainte par défaut qui est définie lorsqu'une nouvelle ligne est insérée: 

Created DATETIME2(3) 
   CONSTRAINT DF_YourTable_Created DEFAULT (SYSDATETIME())

Ainsi, lorsque vous insérez une ligne dans YourTable et ne spécifiez pas de valeur pour Created, il sera fixé à la date actuelle et l'heure.

Le {[7] } est un peu plus de travail, puisque vous devrez écrire un déclencheur pour le cas AFTER UPDATE et le mettre à jour-vous ne pouvez pas déclarer à SQL Server de le faire pour vous....

Modified DATETIME2(3)

, puis

CREATE TRIGGER updateModified
ON dbo.YourTable
AFTER UPDATE 
AS
   UPDATE dbo.YourTable
   SET modified = SYSDATETIME()
   FROM Inserted i
   WHERE dbo.YourTable.PrimaryKey = i.PrimaryKey

Vous avez besoin pour joindre la pseudo-table Inserted qui contient toutes les lignes qui ont été mises à jour avec votre table de base sur votre clé primaire pour cette table.

Et vous devrez créer ce déclencheur AFTER UPDATE pour chaque table dans laquelle vous voulez avoir une colonne modified.

29
répondu marc_s 2015-07-26 08:16:07

Généralement, vous pouvez avoir les colonnes suivantes:

  • LastModifiedBy
  • LastModifiedOn
  • CreatedBy
  • CreatedOn

Où LastModifiedBy et CreatedBy sont des références à un users table (UserID) et LastModifiedOn et CreatetOn colonnes date et le temps de colonnes.

, Vous avez les options suivantes:

  1. Solution sans déclencheurs-j'ai lu quelque part que "la meilleure façon d'écrire des déclencheurs n'est pas d'écrire de tels déclencheurs." et vous devez savoir que généralement Ils nuisent à la performance. Donc, si vous pouvez les éviter, il est préférable de le faire, même en utilisant des déclencheurs peut sembler la chose la plus facile à faire dans certains cas.

    Donc, il suffit d'éditer toutes les instructions INSERT et UPDATE pour inclure les UserID et la date et l'heure actuelles. Si tel user ID ne peut pas être défini (utilisateur anonyme), vous pouvez utiliser 0 à la place et la valeur par défaut des colonnes (au cas où aucun user ID n'est spécifié sera NULL). Lorsque vous voyez NULL les valeurs sont inséré, vous devriez trouver les déclarations "coupables" et les éditer.

  2. Solution avec triggers - vous pouvez créer AFTER INSERT, UPDATE trigger et y remplir les colonnes users. Il est facile d'obtenir la date et l'heure actuelles dans le contexte du déclencheur (utilisez GETUTCDATE() par exemple). Le problème ici est que les déclencheurs ne permettent pas de passer / accepter des paramètres. Donc, comme vous n'insérez pas la valeur user ID et que vous n'êtes pas en mesure de la Passer au déclencheur. Comment trouver l'utilisateur actuel?

    Vous pouvez utiliser SET CONTEXT_INFO et CONTEXT_INFO. Avant toutes les instructions insert et update, Vous devez utiliser le SET CONTEXT_INFO pour ajouter le current user ID au contexte actuel et dans le déclencheur, vous utilisez la fonction CONTEXT_INFO pour l'extraire.

Donc, lorsque vous utilisez des déclencheurs, vous devez à nouveau modifier toutes vos clauses INSERT et UPDATE - c'est pourquoi je préfère ne pas les utiliser.

Quoi qu'il en soit, si vous devez avoir uniquement des colonnes date et heure et non créé / modifié par des colonnes, l'utilisation de déclencheurs est plus durable et plus facile car vous n'allez pas éditer d'autres instructions maintenant et dans le futur.

Avec SQL Server 2016, nous pouvons maintenant utiliser la fonction SESSION_CONTEXT pour lire les détails de la session. Les détails sont réglés à l'aide de sp_set_session_context (comme read-only ou read and write). Les choses sont un peu conviviales: 

EXEC sp_set_session_context 'user_id', 4;  
SELECT SESSION_CONTEXT(N'user_id');

Un bel exemple .

7
répondu gotqn 2017-05-31 14:49:06

Attention, ci-dessus fonctionne bien mais pas dans tous les cas, J'ai perdu beaucoup de temps et j'ai trouvé ça utile: 

create TRIGGER yourtable_update_insert
ON yourtable
AFTER UPDATE 
as
begin
   set nocount on;
   update yourtable set modified=getdate(), modifiedby = suser_sname()
   from  yourtable t 
   inner join inserted i on t.uniqueid=i.uniqueid 
end
go

set nocount on; est nécessaire, sinon vous obtenez le message d'erreur:

Microsoft SQL Server Management Studio

Aucune ligne n'a été mise à jour.

Les données de la ligne 5 n'ont pas été validées. Source D'Erreur: Microsoft.SqlServer.Gestion.DataTools. Message d'erreur: la ou les valeurs de ligne mises à jour ou supprimées ne rendent pas la ligne unique ou modifient plusieurs lignes(2 lignes).

Correct les erreurs et réessayez ou appuyez sur ECHAP pour annuler les modifications.

OK aide

1
répondu pietervnk 2018-09-11 10:03:19