sql-server - multiple - update sql server ejemplo

Insertar actualización activa cómo determinar si insertar o actualizar

Question

Necesito escribir un inserto, activador de actualización en la tabla A que eliminará todas las filas de la tabla B cuya columna (por ejemplo, Desc) tiene valores como el valor insertado / actualizado en la columna de la tabla A (por ejemplo, Col1). ¿Cómo voy a redactarlo para poder manejar tanto los casos de actualización como de inserción? ¿Cómo puedo determinar si el disparador se ejecuta para una actualización o inserción?

Answer 1

Creo que anidado si es un poco confuso y:

Flat es mejor que anidado [El Zen de Python]

;)

DROP TRIGGER IF EXISTS AFTER_MYTABLE

GO

CREATE TRIGGER dbo.AFTER_MYTABLE ON dbo.MYTABLE AFTER INSERT, UPDATE, DELETE 

AS BEGIN 

    --- FILL THE BEGIN/END SECTION FOR YOUR NEEDS.

    SET NOCOUNT ON;

    IF EXISTS(SELECT * FROM INSERTED)  AND EXISTS(SELECT * FROM DELETED) 
        BEGIN PRINT ''UPDATE'' END 
    ELSE IF EXISTS(SELECT * FROM INSERTED)  AND NOT EXISTS(SELECT * FROM DELETED) 
        BEGIN PRINT ''INSERT'' END 
    ELSE IF    EXISTS(SELECT * FROM DELETED) AND NOT EXISTS(SELECT * FROM INSERTED)
        BEGIN PRINT ''DELETED'' END
    ELSE BEGIN PRINT ''NOTHING CHANGED''; RETURN; END  -- NOTHING

END

Answer 2

Después de una gran cantidad de búsquedas, no pude encontrar un ejemplo exacto de un único activador de SQL Server que maneje todas las (3) tres condiciones de las acciones de activación INSERT, UPDATE y DELETE. Finalmente encontré una línea de texto que hablaba del hecho de que cuando se produce un BORRADO o ACTUALIZACIÓN, la tabla eliminada común contendrá un registro para estas dos acciones. En función de esa información, creé una pequeña rutina de Acción que determina por qué se activó el activador. Este tipo de interfaz a veces se necesita cuando hay una configuración común y una acción específica en un disparador INSERTAR versus ACTUALIZAR. En estos casos, crear un disparador separado para la ACTUALIZACIÓN y el INSERT se convertiría en un problema de mantenimiento. (es decir, ¿se activaron ambos desencadenadores correctamente para la corrección de algoritmo de datos común necesaria?)

Con ese fin, me gustaría dar el siguiente fragmento de código de evento de múltiples desencadenantes para manejar INSERTAR, ACTUALIZAR, ELIMINAR en un desencadenante para un Servidor SQL de Microsoft.

CREATE TRIGGER [dbo].[INSUPDDEL_MyDataTable]
ON [dbo].[MyDataTable] FOR INSERT, UPDATE, DELETE
AS 

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with caller queries SELECT statements.
-- If an update/insert/delete occurs on the main table, the number of records affected
-- should only be based on that table and not what records the triggers may/may not
-- select.
SET NOCOUNT ON;

--
-- Variables Needed for this Trigger
-- 
DECLARE @PACKLIST_ID varchar(15)
DECLARE @LINE_NO smallint
DECLARE @SHIPPED_QTY decimal(14,4)
DECLARE @CUST_ORDER_ID varchar(15)
--
-- Determine if this is an INSERT,UPDATE, or DELETE Action
-- 
DECLARE @Action as char(1)
DECLARE @Count as int
SET @Action = ''I'' -- Set Action to ''I''nsert by default.
SELECT @Count = COUNT(*) FROM DELETED
if @Count > 0
    BEGIN
        SET @Action = ''D'' -- Set Action to ''D''eleted.
        SELECT @Count = COUNT(*) FROM INSERTED
        IF @Count > 0
            SET @Action = ''U'' -- Set Action to ''U''pdated.
    END

if @Action = ''D''
    -- This is a DELETE Record Action
    --
    BEGIN
        SELECT @PACKLIST_ID =[PACKLIST_ID]
                    ,@LINE_NO = [LINE_NO]
        FROM DELETED

        DELETE [dbo].[MyDataTable]
        WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
    END
 Else
    BEGIN
            --
            -- Table INSERTED is common to both the INSERT, UPDATE trigger
            --
            SELECT @PACKLIST_ID =[PACKLIST_ID]
                ,@LINE_NO = [LINE_NO]
                ,@SHIPPED_QTY =[SHIPPED_QTY]
                ,@CUST_ORDER_ID = [CUST_ORDER_ID]
            FROM INSERTED 

         if @Action = ''I''
            -- This is an Insert Record Action
            --
            BEGIN
                INSERT INTO [MyChildTable]
                    (([PACKLIST_ID]
                    ,[LINE_NO]
                    ,[STATUS]
                VALUES
                    (@PACKLIST_ID
                    ,@LINE_NO
                    ,''New Record''
                    )
            END
        else
            -- This is an Update Record Action
            --
            BEGIN
                UPDATE [MyChildTable]
                    SET [PACKLIST_ID] = @PACKLIST_ID
                          ,[LINE_NO] = @LINE_NO
                          ,[STATUS]=''Update Record''
                WHERE [PACKLIST_ID]=@PACKLIST_ID AND [LINE_NO]=@LINE_NO
            END
    END   

Answer 3

En el primer escenario, supongo que tu tabla tiene una columna de IDENTIDAD

CREATE TRIGGER [dbo].[insupddel_yourTable] ON [yourTable]
FOR INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT = 0 return
SET NOCOUNT ON;
DECLARE @action nvarchar(10)
SELECT @action = CASE WHEN COUNT(i.Id) > COUNT(d.Id) THEN ''inserted''
                      WHEN COUNT(i.Id) < COUNT(d.Id) THEN ''deleted'' ELSE ''updated'' END
FROM inserted i FULL JOIN deleted d ON i.Id = d.Id

En el segundo escenario, no es necesario usar la columna de IDENTIDAD

CREATE TRIGGER [dbo].[insupddel_yourTable] ON [yourTable]
FOR INSERT, UPDATE, DELETE
AS
IF @@ROWCOUNT = 0 return
SET NOCOUNT ON;
DECLARE @action nvarchar(10),
        @insCount int = (SELECT COUNT(*) FROM inserted),
        @delCount int = (SELECT COUNT(*) FROM deleted)
SELECT @action = CASE WHEN @insCount > @delCount THEN ''inserted''
                      WHEN @insCount < @delCount THEN ''deleted'' ELSE ''updated'' END

Answer 4

Encontré un pequeño error en la solución genial de Grahams:

Debería ser IF COLUMNS_UPDATED () < > 0 - insertar o actualizar
en lugar de> 0 probablemente porque el bit superior se interpreta como el bit de signo entero FIRMADO ... (?). Entonces, en total:

DECLARE @action CHAR(8)  
IF COLUMNS_UPDATED() <> 0 -- delete or update?
BEGIN     
  IF EXISTS (SELECT * FROM deleted) -- updated cols + old rows means action=update       
    SET @action = ''UPDATE''     
  ELSE
    SET @action = ''INSERT'' -- updated columns and nothing deleted means action=insert
END 
ELSE -- delete     
BEGIN
  SET @action = ''DELETE''
END

Answer 5

Esta podría ser una forma más rápida:

DECLARE @action char(1)

IF COLUMNS_UPDATED() > 0 -- insert or update
BEGIN
    IF EXISTS (SELECT * FROM DELETED) -- update
        SET @action = ''U''
    ELSE
        SET @action = ''I''
    END
ELSE -- delete
    SET @action = ''D''

Answer 6

Esto hace el truco para mí:

declare @action_type int;
select @action_type = case
                       when i.id is not null and d.id is     null then 1 -- insert
                       when i.id is not null and d.id is not null then 2 -- update
                       when i.id is     null and d.id is not null then 3 -- delete
                     end
  from      inserted i
  full join deleted  d on d.id = i.id

Como no todas las columnas se pueden actualizar a la vez, puede verificar si una columna en particular se está actualizando por algo como esto:

IF UPDATE([column_name])

Answer 7

Estoy usando lo siguiente, también detecta correctamente las declaraciones de eliminación que no eliminan nada:

CREATE TRIGGER dbo.TR_TableName_TriggerName
    ON dbo.TableName
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS(SELECT * FROM INSERTED)
        -- DELETE
        PRINT ''DELETE'';
    ELSE
    BEGIN
        IF NOT EXISTS(SELECT * FROM DELETED)
            -- INSERT
            PRINT ''INSERT'';
        ELSE
            -- UPDATE
            PRINT ''UPDATE'';
    END
END;

Answer 8

He utilizado esas consultas exists (select * from inserted/deleted) durante mucho tiempo, pero aún no es suficiente para las operaciones CRUD vacías (cuando no hay registros en las tablas inserted y deleted ). Entonces, después de investigar un poco este tema, encontré una solución más precisa:

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''

También es posible usar columns_updated() & power(2, column_id - 1) > 0 para ver si la columna está actualizada, pero no es segura para tablas con gran cantidad de columnas. Utilicé una forma de cálculo un tanto compleja (ver el útil artículo a continuación).

Además, este enfoque aún clasifica incorrectamente algunas actualizaciones como inserciones (si cada columna de la tabla se ve afectada por la actualización), y probablemente clasifique las inserciones donde solo se insertan valores predeterminados como eliminaciones, pero esas son el rey de las operaciones raras (en arrendamiento en mi sistema son). Además de eso, no sé cómo mejorar esta solución en este momento.

• COLUMNS_UPDATED () para activadores de auditoría por Piotr Rodak
• Tratar con máscaras de bits muy grandes por Adam Machanic

Answer 9

Me gustan las soluciones que son "informáticas elegantes". Mi solución aquí golpea los pseudotables [insertados] y [eliminados] una vez para obtener sus estados y pone el resultado en una variable asignada de bits. Entonces, cada posible combinación de INSERTAR, ACTUALIZAR y ELIMINAR se puede probar fácilmente a través del activador con evaluaciones binarias eficientes (a excepción de la improbable combinación INSERTAR o ELIMINAR).

Supone que no importa cuál era la declaración de DML si no se modificaban las filas (lo que debería satisfacer la gran mayoría de los casos). Entonces, si bien no es tan completo como la solución de Roman Pekar, es más eficiente.

Con este enfoque, tenemos la posibilidad de un disparador "FOR INSERT, UPDATE, DELETE" por tabla, que nos da A) control completo sobre el orden de acción yb) una implementación de código por acción aplicable a múltiples acciones. (Obviamente, cada modelo de implementación tiene sus pros y sus contras; deberá evaluar sus sistemas individualmente para saber qué es lo que realmente funciona mejor).

Tenga en cuenta que las instrucciones "exists (select * from" inserted / deleted »)" son muy eficientes ya que no hay acceso al disco ( 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 10

Muchas de estas sugerencias no se tienen en cuenta si ejecuta una declaración de eliminación que no borra nada.
Digamos que intentas eliminar donde una ID es igual a algún valor que no existe en la tabla.
Aún se llama su desencadenante, pero no hay nada en las tablas Eliminado o Insertado.

Use esto para estar seguro:

--Determine if this is an INSERT,UPDATE, or DELETE Action or a "failed delete".
DECLARE @Action as char(1);
    SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED)
                         AND EXISTS(SELECT * FROM DELETED)
                        THEN ''U''  -- Set Action to Updated.
                        WHEN EXISTS(SELECT * FROM INSERTED)
                        THEN ''I''  -- Set Action to Insert.
                        WHEN EXISTS(SELECT * FROM DELETED)
                        THEN ''D''  -- Set Action to Deleted.
                        ELSE NULL -- Skip. It may have been a "failed delete".   
                    END)

Un agradecimiento especial a @KenDog y @Net_Prog por sus respuestas.
Lo construí a partir de sus scripts.

Answer 11

Prueba esto..

ALTER TRIGGER ImportacionesGS ON dbo.Compra 
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  -- idCompra is PK
  DECLARE @vIdCompra_Ins INT,@vIdCompra_Del INT
  SELECT @vIdCompra_Ins=Inserted.idCompra FROM Inserted
  SELECT @vIdCompra_Del=Deleted.idCompra FROM Deleted
  IF (@vIdCompra_Ins IS NOT NULL AND @vIdCompra_Del IS NULL)  
  Begin
     -- Todo Insert
  End
  IF (@vIdCompra_Ins IS NOT NULL AND @vIdCompra_Del IS NOT NULL)
  Begin
     -- Todo Update
  End
  IF (@vIdCompra_Ins IS NULL AND @vIdCompra_Del IS NOT NULL)
  Begin
     -- Todo Delete
  End
END

Answer 12

Si es MS SQL Server ...

Los disparadores tienen tablas INSERTED y DELETED especiales para rastrear datos "antes" y "después". Entonces puede usar algo como IF EXISTS (SELECT * FROM DELETED) para detectar una actualización. Solo tiene filas en DELETED en la actualización, pero siempre hay filas en INSERTED .

Busque "insertado" en CREATE TRIGGER

Editar, 23 de noviembre de 2011

Después de comentar, esta respuesta es solo para disparadores INSERTED y UPDATED .
Obviamente, los activadores DELETE no pueden tener "siempre filas en INSERTED " como dije antes

Answer 13

Un posible problema con las dos soluciones que se ofrecen es que, según cómo estén escritas, una consulta de actualización puede actualizar cero registros y una consulta de inserción puede insertar cero registros. En estos casos, los conjuntos de registros insertados y eliminados estarán vacíos. En muchos casos, si los conjuntos de registros insertados y eliminados están vacíos, es posible que desee salir del activador sin hacer nada.

Answer 14

aunque también me gusta la respuesta publicada por @Alex, ofrezco esta variación a la solución de @ Graham anterior

esto utiliza exclusivamente la existencia de registros en las tablas INSERTED y UPDATED, en lugar de utilizar COLUMNS_UPDATED para la primera prueba. También proporciona al programador paranoico alivio sabiendo que el caso final se ha considerado ...

declare @action varchar(4)
    IF EXISTS (SELECT * FROM INSERTED)
    BEGIN
        IF EXISTS (SELECT * FROM DELETED) 
            SET @action = ''U''  -- update
        ELSE
            SET @action = ''I''  --insert
        END
    ELSE IF EXISTS (SELECT * FROM DELETED)
        SET @action = ''D''  -- delete
    else 
        set @action = ''noop'' --no records affected
--print @action

obtendrá un NOOP con una declaración como la siguiente:

update tbl1 set col1=''cat'' where 1=2

Answer 15

solo manera simple

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 16

Solución rápida MySQL

Por cierto: estoy usando MySQL PDO.

(1) En una tabla de incremento automático solo obtenga el valor más alto (mi nombre de columna = id) de la columna incrementada una vez que cada script se ejecute primero:

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

(2) Ejecute la consulta MySQL como lo haría normalmente, y convierta el resultado en entero, por ejemplo:

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

(3) Después de la consulta "INSERT INTO ... ON DUPLICATE KEY UPDATE", obtenga la última ID insertada de la forma que prefiera, por ejemplo:

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

(4) Compare y reaccione: si el últimoInsertId es más alto que el más alto de la tabla, probablemente sea un INSERT, ¿verdad? Y viceversa.

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

Sé que es rápido y tal vez sucio. Y es una publicación anterior. Pero, oye, estaba buscando una solución por mucho tiempo, y tal vez alguien encuentre mi camino algo útil de todos modos. ¡Todo lo mejor!

Answer 17

CREATE TRIGGER dbo.TableName_IUD
ON dbo.TableName
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
    SET NOCOUNT ON;

    --
    -- Check if this is an INSERT, UPDATE or DELETE Action.
    -- 
    DECLARE @action as char(1);

    SET @action = ''I''; -- Set Action to Insert by default.
    IF EXISTS(SELECT * FROM DELETED)
    BEGIN
        SET @action = 
            CASE
                WHEN EXISTS(SELECT * FROM INSERTED) THEN ''U'' -- Set Action to Updated.
                ELSE ''D'' -- Set Action to Deleted.       
            END
    END
    ELSE 
        IF NOT EXISTS(SELECT * FROM INSERTED) RETURN; -- Nothing updated or inserted.

    ...

    END

Answer 18

DECLARE @INSERTEDCOUNT INT,
        @DELETEDCOUNT INT

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

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

SI su actualización

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 1

si su inserción

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 0

Answer 19

Declare @Type varchar(50)='''';
IF EXISTS (SELECT * FROM inserted) and  EXISTS (SELECT * FROM deleted)
BEGIN
    SELECT @Type = ''UPDATE''
END
ELSE IF EXISTS(SELECT * FROM inserted)
BEGIN
    SELECT @Type = ''INSERT''
END
ElSE IF EXISTS(SELECT * FROM deleted)
BEGIN
    SELECT @Type = ''DELETE''
END

Answer 20

declare @insCount int
declare @delCount int
declare @action char(1)

select @insCount = count(*) from INSERTED
select @delCount = count(*) from DELETED

    if(@insCount > 0 or @delCount > 0)--if something was actually affected, otherwise do nothing
    Begin
        if(@insCount = @delCount)
            set @action = ''U''--is update
        else if(@insCount > 0)
            set @action = ''I'' --is insert
        else
            set @action = ''D'' --is delete

        --do stuff here
    End