studio - sql express limit database



Funzione di aggiornamento nel trigger TSQL (4)

Il trigger di aggiornamento verrà attivato su tutte le dichiarazioni di aggiornamento. le righe interessate sono disponibili all'interno del trigger nelle tabelle "inserite" e "eliminate". È possibile confrontare i valori vecchi e nuovi confrontando le colonne PK nelle due tabelle (se si dispone di un PK). La tabella effettiva rimane invariata fino al termine dell'esecuzione.

https://ffff65535.com

Ho una domanda sull'aggiornamento della funzione TSQL. Ad esempio, ho una tabella con un nome campo. Se controllo se il nome del campo è stato modificato o meno in un trigger di After Update piace questo:

  if Update(Name)
  Begin
    -- process
  End

L'aggiornamento restituirà ancora TRUE anche se il nome non viene modificato? La seguente dichiarazione di aggiornamento lo aggiornerà con lo stesso valore:

  SELECT @v_Name = Name From MyTable Where Id = 1;
  Update MyTable Set Name = @v_Name where Id = 1;

Se Update () restituisce TRUE anche il valore di Name non viene modificato, devo confrontare il valore nelle tabelle virtuali inserite ed eliminate per scoprire se il valore è realmente cambiato?

A proposito, le tabelle inserite e cancellate sono virtuali e possono contenere più di una riga di dati se più di una riga di dati viene modificata da una istruzione TSQL INSERT o UPDATE. Nel caso di più di un record, i numeri di conteggio delle righe nelle tabelle virtuali inserite ed eliminate sono gli stessi e qual è il vero significato di Update (Nome) come TRUE? Significa che almeno uno è cambiato? Oppure Aggiorna (nome) significa che il campo Nome è stato impostato dall'istruzione Update indipendentemente dal fatto che il valore sia stato modificato?

Il server SQL che utilizzo è Microsoft SQL 2005.


Sono d'accordo sul modo migliore per determinare se un valore di colonna è effettivamente cambiato (al contrario di essere aggiornato con lo stesso valore) è quello di fare un confronto dei valori delle colonne nelle tabelle pseudo cancellate e inserite. Tuttavia, questo può essere un vero problema se si desidera controllare più di poche colonne.

Ecco un trucco che ho trovato in un codice che mantenevo (non conosco l'autore originale): utilizza UNION e GROUP BY con una clausola HAVING per determinare quali colonne sono state modificate.

ad esempio, nel trigger, per ottenere gli ID delle righe che sono cambiate:

SELECT SampleID
FROM 
    (
        SELECT SampleID, SampleName
        FROM deleted

        -- NOTE: UNION, not UNION ALL.  UNION by itself removes duplicate 
        --  rows.  UNION ALL includes duplicate rows.
        UNION 

        SELECT SampleID, SampleName
        FROM inserted
    ) x
GROUP BY SampleID
HAVING COUNT(*) > 1

Questo è troppo lavoro quando si controlla solo se una singola colonna è cambiata. Ma se stai controllando 10 o 20 colonne il metodo UNION è molto meno lavoro di

WHERE COALESCE(Inserted.Column1, '') <> COALESCE(Deleted.Column1, '')
    OR COALESCE(Inserted.Column2, '') <> COALESCE(Deleted.Column2, '')
    OR COALESCE(Inserted.Column3, '') <> COALESCE(Deleted.Column3, '')
    OR ...

I trigger sono complicati e devi pensare in grande quantità quando ne stai creando uno. Un trigger si attiva una volta per ogni istruzione UPDATE. Se l'istruzione UPDATE aggiorna più righe, il trigger verrà attivato una sola volta. La funzione UPDATE () restituisce true per una colonna quando tale colonna è inclusa nell'istruzione UPDATE. Questa funzione aiuta a migliorare l'efficienza dei trigger consentendo di evitare la logica SQL quando quella colonna non è nemmeno inclusa nell'istruzione update. Non ti dice se il valore è cambiato per una colonna in una data riga.

Ecco una tabella di esempio ...

CREATE TABLE tblSample
(
    SampleID INT PRIMARY KEY,
    SampleName VARCHAR(10),
    SampleNameLastChangedDateTime DATETIME,
    Parent_SampleID INT
)

Se il seguente SQL è stato utilizzato contro questa tabella:

UPDATE tblSample SET SampleName = 'hello'

..e un trigger AFTER INSERT, UPDATE era in effetti, questa particolare istruzione SQL avrebbe sempre valutato la funzione UPDATE come segue ...

IF UPDATE(SampleName) --aways evaluates to TRUE
IF UPDATE(SampleID)  --aways evaluates to FALSE
IF UPDATE(Parent_SampleID) --aways evaluates to FALSE

Si noti che UPDATE (SampleName) sarà sempre true per questa istruzione SQL, indipendentemente da ciò che erano prima i valori SampleName. Restituisce true perché l'istruzione UPDATE include la colonna SampleName nella sezione SET di quella clausola e non in base a ciò che i valori erano prima o dopo. La funzione UPDATE () non determinerà se i valori sono cambiati. Se si desidera eseguire azioni in base alla modifica dei valori, sarà necessario utilizzare SQL e confrontare le righe inserite e cancellate.

Ecco un approccio per mantenere sincronizzata un'ultima colonna aggiornata:

--/*
IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL 
  DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
GO
--*/

CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample
  AFTER INSERT, UPDATE 
AS
BEGIN --Trigger

  IF UPDATE(SampleName)  
    BEGIN
      UPDATE tblSample SET
      SampleNameLastChangedDateTime = CURRENT_TIMESTAMP
      WHERE
        SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))
    END

END --Trigger

La logica per determinare se la riga è stata aggiornata è nella clausola WHERE sopra. Questo è il vero controllo che devi fare. La mia logica sta usando COALESCE per gestire valori NULL e INSERTS.

...
WHERE
  SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))

Si noti che il controllo IF UPDATE () viene utilizzato per migliorare l'efficienza del trigger per quando la colonna SampleName NON viene aggiornata. Se un'istruzione SQL ha aggiornato la colonna Parent_SampleID per l'istanza, allora il controllo IF UPDATE (SampleName) potrebbe aggirare la logica più complessa in tale istruzione IF quando non è necessario eseguire. Considera l'uso di UPDATE () quando è appropriato ma non per il motivo sbagliato.

Inoltre, ti rendi conto che, a seconda dell'architettura, la funzione UPDATE potrebbe non esserti utile. Se l'architettura del codice utilizza un livello intermedio che aggiorna sempre tutte le colonne in una riga di una tabella con i valori nell'oggetto business quando l'oggetto viene salvato, la funzione UPDATE () in un trigger diventa inutile. In tal caso, è probabile che il codice aggiorni sempre tutte le colonne con ogni istruzione UPDATE emessa dal livello intermedio. Stando così le cose, la funzione UPDATE (columnname) valuterà sempre true quando gli oggetti business vengono salvati perché tutti i nomi delle colonne sono sempre inclusi nelle istruzioni di aggiornamento. In tal caso, non sarebbe utile utilizzare UPDATE () nel trigger e sarebbe solo un overhead aggiuntivo in quel trigger per la maggior parte del tempo.

Ecco alcuni SQL per giocare con il trigger sopra:

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 1, 'One'
UNION SELECT 2, 'Two'
UNION SELECT 3, 'Three'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample

/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
*/

GO

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 4, 'Foo'
UNION SELECT 5, 'Five'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
4       Foo    2010-10-27 14:52:42.587
5       Five   2010-10-27 14:52:42.587
*/

GO

UPDATE tblSample SET SampleName = 'Foo' 

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample 
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Foo    2010-10-27 14:52:42.657
2       Foo    2010-10-27 14:52:42.657
3       Foo    2010-10-27 14:52:42.657
4       Foo    2010-10-27 14:52:42.587
5       Foo    2010-10-27 14:52:42.657
*/
GO

UPDATE tblSample SET SampleName = 'Not Prime' WHERE SampleID IN (1,4)

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Not Prime  2010-10-27 14:52:42.680
2       Foo        2010-10-27 14:52:42.657
3       Foo        2010-10-27 14:52:42.657
4       Not Prime  2010-10-27 14:52:42.680
5       Foo        2010-10-27 14:52:42.657
*/

--Clean up...
DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
DROP TABLE tblSample

L'utente GBN ha suggerito quanto segue:

IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON I.key = D.key
    WHERE
        D.valuecol <> I.valuecol --watch for NULLs!
    )
   blah

Il suggerimento di GBN di usare un IF (clausola EXISTS (... e mettere la logica in quell'istruzione IF se esistessero le righe che erano cambiate potrebbe funzionare. Questo approccio sparerà per TUTTE le righe incluse nel trigger anche se solo alcune delle righe erano effettivamente modificato (che potrebbe essere appropriato per la soluzione, ma potrebbe anche non essere appropriato se si desidera eseguire solo qualcosa sulle righe in cui i valori sono stati modificati). Se è necessario eseguire qualcosa sulle righe in cui si è verificata una modifica effettiva, è necessaria una logica diversa nel tuo SQL che ha fornito.

Nei miei esempi sopra, quando viene emessa l'istruzione UPDATE tblSample SET SampleName = 'Foo' e la quarta riga è già 'foo', l'utilizzo dell'approccio di GBN per aggiornare una colonna "last modified datetime" aggiornerebbe anche la quarta riga, che non sarebbe essere appropriato in questo caso.


Penso che il seguente codice sia migliore degli esempi precedenti perché si concentra solo sulle colonne che si desidera controllare in modo sintetico ed efficiente.

Determina se un valore è cambiato solo nelle colonne specificate. Non ho studiato le sue prestazioni rispetto alle altre soluzioni, ma sta funzionando bene nel mio database.

Usa l'operatore set EXCEPT per restituire qualsiasi riga dalla query di sinistra che non si trova anche nella query corretta. Questo codice può essere utilizzato nei trigger INSERT e UPDATE.

La colonna "PrimaryKeyID" è la chiave primaria della tabella (può essere più colonne) ed è necessaria per abilitare la corrispondenza tra i due set.

-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT  PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM inserted
          EXCEPT
          SELECT PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM deleted )    -- Tests for modifications to fields that we are interested in
BEGIN
          -- Put code here that does the work in the trigger

END

Se si desidera utilizzare le righe modificate nella logica di trigger successiva, di solito inserisco i risultati della query EXCEPT in una variabile di tabella a cui si può fare riferimento in seguito.

Spero che questo sia interessante :-)





tsql