Insert-Update-Trigger, wie man feststellt, ob Insert oder Update

Question

Ich muss einen Einfüge- und Aktualisierungs-Trigger für Tabelle A schreiben, der alle Zeilen aus Tabelle B löscht, deren eine Spalte (z. B. Desc) Werte enthält, die dem Wert entsprechen, der in die Spalte der Tabelle A (z. B. Col1) eingefügt/aktualisiert wurde. Wie würde ich vorgehen, um es so zu schreiben, dass ich sowohl Aktualisierungs- als auch Einfügefälle behandeln kann. Wie würde ich feststellen, ob der Trigger für eine Aktualisierung oder eine Einfügung ausgeführt wird.

Answer 1

Einfacher Weg

CREATE TRIGGER [dbo].[WO_EXECUTION_TRIU_RECORD] ON [dbo].[WO_EXECUTION]
WITH EXECUTE AS CALLER
FOR INSERT, UPDATE
AS
BEGIN  

  select @vars = [column] from inserted 
  IF UPDATE([column]) BEGIN
    -- do update action base on @vars 
  END ELSE BEGIN
    -- do insert action base on @vars 
  END

END

Answer 2

Ich mag Lösungen, die "computerwissenschaftlich elegant" sind. Meine Lösung hier greift auf die Pseudotabellen [inserted] und [deleted] jeweils einmal zu, um ihren Status zu ermitteln, und legt das Ergebnis in einer Bitmap-Variablen ab. Dann kann jede mögliche Kombination von INSERT, UPDATE und DELETE im gesamten Trigger mit effizienten binären Auswertungen getestet werden (mit Ausnahme der unwahrscheinlichen INSERT- oder DELETE-Kombination).

Dabei wird davon ausgegangen, dass es keine Rolle spielt, was die DML-Anweisung war, wenn keine Zeilen geändert wurden (was in den allermeisten Fällen der Fall sein dürfte). Damit ist sie zwar nicht so vollständig wie die Lösung von Roman Pekar, aber effizienter.

Mit diesem Ansatz haben wir die Möglichkeit eines "FOR INSERT, UPDATE, DELETE"-Triggers pro Tabelle, was uns A) eine vollständige Kontrolle über die Reihenfolge der Aktionen und b) eine Code-Implementierung pro Aktion, die auf mehrere Aktionen anwendbar ist, ermöglicht. (Natürlich hat jedes Implementierungsmodell seine Vor- und Nachteile; Sie müssen Ihre Systeme individuell bewerten, um herauszufinden, was wirklich am besten funktioniert).

Beachten Sie, dass die Anweisungen "exists (select * from "inserted/deleted")" sehr effizient sind, da kein Plattenzugriff erfolgt ( https://social.msdn.microsoft.com/Forums/en-US/01744422-23fe-42f6-9ab0-a255cdf2904a ).

use tempdb
;
create table dbo.TrigAction (asdf int)
;
GO
create trigger dbo.TrigActionTrig
on dbo.TrigAction
for INSERT, UPDATE, DELETE
as
declare @Action tinyint
;
-- Create bit map in @Action using bitwise OR "|"
set @Action = (-- 1: INSERT, 2: DELETE, 3: UPDATE, 0: No Rows Modified 
  (select case when exists (select * from inserted) then 1 else 0 end)
| (select case when exists (select * from deleted ) then 2 else 0 end))
;
-- 21 <- Binary bit values
-- 00 -> No Rows Modified
-- 01 -> INSERT -- INSERT and UPDATE have the 1 bit set
-- 11 -> UPDATE <
-- 10 -> DELETE -- DELETE and UPDATE have the 2 bit set

raiserror(N'@Action = %d', 10, 1, @Action) with nowait
;
if (@Action = 0) raiserror(N'No Data Modified.', 10, 1) with nowait
;
-- do things for INSERT only
if (@Action = 1) raiserror(N'Only for INSERT.', 10, 1) with nowait
;
-- do things for UPDATE only
if (@Action = 3) raiserror(N'Only for UPDATE.', 10, 1) with nowait
;
-- do things for DELETE only
if (@Action = 2) raiserror(N'Only for DELETE.', 10, 1) with nowait
;
-- do things for INSERT or UPDATE
if (@Action & 1 = 1) raiserror(N'For INSERT or UPDATE.', 10, 1) with nowait
;
-- do things for UPDATE or DELETE
if (@Action & 2 = 2) raiserror(N'For UPDATE or DELETE.', 10, 1) with nowait
;
-- do things for INSERT or DELETE (unlikely)
if (@Action in (1,2)) raiserror(N'For INSERT or DELETE.', 10, 1) with nowait
-- if already "return" on @Action = 0, then use @Action < 3 for INSERT or DELETE
;
GO

set nocount on;

raiserror(N'
INSERT 0...', 10, 1) with nowait;
insert dbo.TrigAction (asdf) select top 0 object_id from sys.objects;

raiserror(N'
INSERT 3...', 10, 1) with nowait;
insert dbo.TrigAction (asdf) select top 3 object_id from sys.objects;

raiserror(N'
UPDATE 0...', 10, 1) with nowait;
update t set asdf = asdf /1 from dbo.TrigAction t where asdf <> asdf;

raiserror(N'
UPDATE 3...', 10, 1) with nowait;
update t set asdf = asdf /1 from dbo.TrigAction t;

raiserror(N'
DELETE 0...', 10, 1) with nowait;
delete t from dbo.TrigAction t where asdf < 0;

raiserror(N'
DELETE 3...', 10, 1) with nowait;
delete t from dbo.TrigAction t;
GO

drop table dbo.TrigAction
;
GO

Answer 3

DECLARE @INSERTEDCOUNT INT,
        @DELETEDCOUNT INT

SELECT @INSERTEDCOUNT = COUNT([YourColumnName]) FROM inserted

SELECT @DELETEDCOUNT = COUNT([YourColumnName]) FROM deleted

WENN seine Aktualisierung

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 1

wenn seine Einfügung

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 0

Answer 4

Schnelllösung MySQL

Im Übrigen: Ich verwende MySQL PDO.

(1) In einer Autoinkrement-Tabelle wird bei jedem Skriptdurchlauf zuerst der höchste Wert (mein Spaltenname = id) aus der inkrementierten Spalte ermittelt:

$select = "
    SELECT  MAX(id) AS maxid
    FROM    [tablename]
    LIMIT   1
";

(2) Führen Sie die MySQL-Abfrage wie gewohnt aus und wandeln Sie das Ergebnis in eine Ganzzahl um, z. B.:

$iMaxId = (int) $result[0]->maxid;

(3) Nach der Eingabe von "INSERT INTO ... ON DUPLICATE KEY UPDATE"-Abfrage die zuletzt eingefügte ID auf die von Ihnen bevorzugte Weise abrufen, z.B.:

$iLastInsertId = (int) $db->lastInsertId();

(4) Vergleichen und reagieren: Wenn die lastInsertId höher ist als die höchste in der Tabelle, handelt es sich wahrscheinlich um eine INSERT, richtig? Und vice versa.

if ($iLastInsertId > $iMaxObjektId) {
    // IT'S AN INSERT
}
else {
    // IT'S AN UPDATE
}

Ich weiß, es ist schnell und vielleicht schmutzig. Und es ist ein alter Beitrag. Aber, hey, ich habe lange nach einer Lösung gesucht, und vielleicht findet jemand meinen Weg trotzdem irgendwie nützlich. Alles Gute und viel Erfolg!

Answer 5

Ich habe sie benutzt exists (select * from inserted/deleted) Abfragen für eine lange Zeit, aber es ist immer noch nicht genug für leere CRUD-Operationen (wenn es keine Datensätze in inserted y deleted Tabellen). Nachdem ich dieses Thema ein wenig erforscht habe, habe ich eine genauere Lösung gefunden:

declare
    @columns_count int = ?? -- number of columns in the table,
    @columns_updated_count int = 0

-- this is kind of long way to get number of actually updated columns
-- from columns_updated() mask, it's better to create helper table
-- or at least function in the real system
with cte_columns as (
    select @columns_count as n
    union all
    select n - 1 from cte_columns where n > 1
), cte_bitmasks as (
    select
        n,
        (n - 1) / 8 + 1 as byte_number,
        power(2, (n - 1) % 8) as bit_mask
    from cte_columns
)
select
    @columns_updated_count = count(*)
from cte_bitmasks as c
where
    convert(varbinary(1), substring(@columns_updated_mask, c.byte_number, 1)) & c.bit_mask > 0

-- actual check
if exists (select * from inserted)
    if exists (select * from deleted)
        select @operation = 'U'
    else
        select @operation = 'I'
else if exists (select * from deleted)
    select @operation = 'D'
else if @columns_updated_count = @columns_count
    select @operation = 'I'
else if @columns_updated_count > 0
    select @operation = 'U'
else
    select @operation = 'D'

Es ist auch möglich, Folgendes zu verwenden columns_updated() & power(2, column_id - 1) > 0 um zu sehen, ob die Spalte aktualisiert wurde, aber es ist nicht sicher für Tabellen mit einer großen Anzahl von Spalten. Ich habe eine etwas komplexere Berechnungsmethode verwendet (siehe hilfreicher Artikel unten).

Außerdem werden bei diesem Ansatz immer noch einige Aktualisierungen fälschlicherweise als Einfügungen klassifiziert (wenn jede Spalte in der Tabelle von der Aktualisierung betroffen ist), und wahrscheinlich werden Einfügungen, bei denen nur Standardwerte eingefügt werden, als Löschungen klassifiziert, aber das sind sehr seltene Operationen (zumindest in meinem System). Abgesehen davon weiß ich im Moment nicht, wie man diese Lösung verbessern kann.

• COLUMNS_UPDATED() für Audit-Trigger von Piotr Rodak
• Der Umgang mit sehr großen Bitmasken von Adam Machanic