Wie Sie verhindern, dass updates für eine Tabelle, mit einer Ausnahme für eine situation

Ich habe eine Tabelle, die Datensätze enthält, die Teil einer Rechnung. Ich kann Ihnen sagen, welche sind bereits Teil einer Rechnung, weil der Tisch hat eine BillId Spalte, die aktualisiert wird durch die Anwendung code, wenn das passiert. Ich möchte verhindern, dass Aktualisierungen zu irgendeinem Datensatz sein, der eine nicht-null-BillId. Ich denke, dass die folgenden sollte sich darum kümmern, dass:

CREATE TRIGGER [Item_Update_AnyBilled]
ON [dbo].[Item]
FOR UPDATE
AS 
BEGIN
SET NOCOUNT ON;
DECLARE @AnyBilled BIT;

SELECT  TOP(1) @AnyBilled = 1
  FROM  inserted i
        JOIN deleted d ON i.ItemId = d.ItemId
 WHERE  d.BillId IS NOT NULL; 

IF COALESCE(@AnyBilled, 0) = 1 BEGIN
    RAISERROR(2870486, 16, 1);  -- Cannot update a record that is part of a bill.
    ROLLBACK TRANSACTION;
END;
END;

Jedoch, dort ist eine Falte. Die Tabelle "Artikel" hat auch eine DATETIME-Spalte Geändert wird, und ein trigger updates.

CREATE TRIGGER [dbo].Item_Update_Modified 
ON  [dbo].[Item] 
AFTER UPDATE
AS 
BEGIN
SET NOCOUNT ON;

UPDATE a
   SET Modified = getdate()
  FROM Item a JOIN inserted i ON i.ItemId = a.ItemId
END

Mit diesen Trigger an Ort und Stelle, das hinzufügen eines Elements zu einer Rechnung immer verursacht, die von RAISERROR ausgelöst. Vermutlich weil, wenn die BillId gefüllt ist, Item_Update_AnyBilled lässt es durch weil die gelöscht.BillId ist NULL, aber die Item_Update_Modified dann hingerichtet wird, und dass sekundäre Ursachen ändern Item_Update_AnyBilled zu bekommen, wieder ausgeführt, und auch dieses mal gelöscht.BillId ist nicht mehr NULL.

Wie kann ich verhindern, dass updates auf die Tabelle "Artikel", außer in dem Fall, wo die BillId wird aufgefüllt, oder, wenn die änderung nur auf die Spalte Geändert?

Ich würde es vorziehen, eine Lösung, die nicht verlangen, mich zu vergleichen, die inserted-und deleted-Werte für jede Spalte (oder verwenden Sie COLUMNS_UPDATED()) schaffen würde, die ein Wartungs-Problem (jemand hätte daran zu erinnern, zu aktualisieren, wird der trigger jedes mal eine neue Spalte Hinzugefügt oder aus der Tabelle gelöscht). Ich bin mit SQL Server 2005.

InformationsquelleAutor Mark Freeman | 2012-02-29

  1. 5

    Warum nicht ein INSTEAD OF auslösen? Es erfordert ein bisschen mehr Arbeit (nämlich eine wiederholte UPDATE - Anweisung) aber jedes mal, wenn Sie können verhindern, dass Arbeit, statt geschehen lassen und dann Rollen Sie zurück, wirst du besser dran.

    CREATE TRIGGER [dbo].[Item_BeforeUpdate_AnyBilled]
ON [dbo].[Item]
INSTEAD OF UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  IF EXISTS 
  (
     SELECT 1 FROM inserted i
       JOIN deleted AS d ON i.ItemId = d.ItemId
       WHERE d.BillId IS NULL -- it was NULL before, may not be NULL now
  )
  BEGIN
     UPDATE src 
       SET col1 = i.col1 --, ... other columns
          ModifiedDate = CURRENT_TIMESTAMP -- this eliminates need for other trigger
       FROM dbo.Item AS src
       INNER JOIN inserted AS i
       ON i.ItemId = src.ItemId
       AND (criteria to determine if at least one column has changed);
  END
  ELSE
  BEGIN
     RAISERROR(...);
  END
END
GO

    Diese nicht perfekt passen. Die Kriterien, die ich verlassen habe aus übrig ist für einen Grund: es kann sehr Komplex werden, um zu bestimmen, ob ein Spaltenwert geändert hat, hängt von dem Datentyp ab, ob die Spalte NULL sein kann, etc. AFAIK die built-in trigger-Funktionen können nur sagen, ob eine bestimmte Spalte angegeben wurde, nicht, ob der Wert tatsächlich geändert-vor.

    BEARBEITEN wenn man bedenkt, dass Sie nur besorgt über die anderen Spalten, die aktualisiert werden, wegen der after-trigger, ich denke, die folgenden INSTEAD OF auslösen kann als Ersatz für Ihren vorhandenen Trigger und befassen sich auch mit mehreren aktualisierten Zeilen auf einmal (einige ohne die Erfüllung Ihrer Kriterien):

    CREATE TRIGGER [dbo].[Item_BeforeUpdate_AnyBilled]
ON [dbo].[Item]
INSTEAD OF UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE src SET col1 = i.col1 --, ... other columns,
     ModifiedDate = CURRENT_TIMESTAMP
     FROM dbo.Item AS src
     INNER JOIN inserted AS i
     ON src.ItemID = i.ItemID
     INNER JOIN deleted AS d
     ON i.ItemID = d.ItemID 
     WHERE d.BillID IS NULL; 

  IF @@ROWCOUNT = 0
  BEGIN
    RAISERROR(...);
  END
END
GO
    Würde so etwas (für jede Spalte in der Tabelle) ausreichen, um die Kriterien zu bestimmen, ob mindestens eine Spalte geändert hat"? ODER (src).[ColumnName] NULL IST UND ich.[ColumnName] IST NICHT NULL) or (src).[ColumnName] NICHT NULL IST UND ich.[ColumnName] IST NULL) ODER src.[ColumnName] != ich.[ColumnName]
    Für NULLable Spalten ja, Sie würde tun müssen, um eine "null" wurde nicht null oder nicht null wurde "null" prüfen Sie aber auch, wenn ein Wert geändert wurde. PRÜFSUMME kann einfacher sein, nachdem Sie festgestellt haben, dass mindestens eine Zeile, die aktualisiert werden sollten. Diese werden zu 99% zuverlässig ist, aber produzieren können dieselben Werte für andere Zeilen. Lassen Sie mich über diese denken ein bisschen und update meine Antwort.
    "WO d....ItemID IST NULL;" sollte sein: "WO d....BillId IST NULL;". Egal, wenn ich führen Sie die CREATE TRIGGER bekomme ich diese: kann Nicht erstellen INSTEAD OF DELETE oder INSTEAD OF UPDATE TRIGGER 'Item_BeforeUpdate_AnyBilled' auf Tabelle 'dbo.Element". Dies ist, da die Tabelle einen FOREIGN KEY mit kaskadierenden DELETE-oder UPDATE.
    Sorry, transkribiert. Jede chance, die Sie verwalten können Ihre löschweitergaben mit einem trigger anstelle der expliziten cascade-option? Leider denke ich nicht, dass dies funktionieren wird, als auch in einem after-trigger.
    Ich könnte ersetzen die Kaskade mit einem anderen auslösen. Ich kann besuchen Sie diese in Zukunft, wie ich deine Lösung besser, weil es entfällt die Notwendigkeit zu vergleichen, die inserted-und deleted-Werte. Allerdings habe ich meinen AFTER UPDATE-trigger arbeiten und ich brauche zu bewegen, gegen andere feuert. Ich habe eine TODO-Element in den code mit einem Verweis auf dieser Seite. Danke!

    InformationsquelleAutor Aaron Bertrand

Schreibe einen Kommentar