Вставить обновление триггера, как определить, вставлять или обновлять

Ответ 1

Если это MS SQL Server...

Триггеры имеют специальные таблицы INSERTED и DELETED для отслеживания данных "до" и "после". Таким образом, вы можете использовать что-то вроде IF EXISTS (SELECT * FROM DELETED) для обнаружения обновления. У вас есть только строки в DELETED при обновлении, но всегда есть строки в INSERTED.

Найдите "вставленный" в CREATE TRIGGER

Изменить, 23.11.2011.

После комментария этот ответ предназначен только для INSERTED и UPDATED триггеров.
Очевидно, триггеры DELETE не могут иметь "всегда строки в INSERTED", как я сказал выше

Ответ 2

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

Ответ 3

Многие из этих предложений не учитываются при запуске инструкции удаления, которая ничего не удаляет.
Скажем, вы пытаетесь удалить, где идентификатор равен некоторому значению, которое не существует в таблице.
Ваш триггер еще вызван, но в таблицах Deleted или Inserted ничего нет.

Используйте это, чтобы быть в безопасности:

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

Особая благодарность @KenDog и @Net_Prog за их ответы.
Я построил это из своих скриптов.

Ответ 4

Я использую следующее: он также корректно определяет операторы удаления, которые ничего не удаляют:

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;

Ответ 5

После большого поиска я не смог найти точный пример одного триггера SQL Server, который обрабатывает все (3) три условия триггерных действий INSERT, UPDATE и DELETE. Наконец, я нашел строку текста, в которой говорилось о том, что, когда происходит DELETE или UPDATE, общая таблица DELETED будет содержать запись для этих двух действий. Основываясь на этой информации, я создал небольшую процедуру действий, которая определяет, почему триггер был активирован. Этот тип интерфейса иногда необходим, когда в триггере INSERT и UPDATE есть как общая конфигурация, так и определенное действие. В этих случаях создание отдельного триггера для UPDATE и INSERT станет проблемой обслуживания. (т.е. были ли оба триггера корректно обновлены для исправления необходимого алгоритма общих данных?)

С этой целью я хотел бы предоставить следующий фрагмент кода события с несколькими триггерами для обработки INSERT, UPDATE, DELETE в одном триггере для Microsoft SQL Server.

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][email protected]_ID AND [LINE_NO][email protected]_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][email protected]_ID AND [LINE_NO][email protected]_NO
            END
    END   

Ответ 6

Я считаю, что вложенный, если немного запутанный, и:

Плоский лучше, чем вложенный [Zen of 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

Ответ 7

Попробуйте это.

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

Ответ 8

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

Ответ 9

Потенциальная проблема с двумя предложенными решениями заключается в том, что в зависимости от того, как они записаны, запрос обновления может обновлять нулевые записи, а запрос вставки может вставлять нулевые записи. В этих случаях вложенные и удаленные наборы записей будут пустыми. Во многих случаях, если оба набора Inserted и Deleted пусты, вы можете просто выйти из триггера, ничего не делая.

Ответ 10

Это может быть более быстрый способ:

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'

Ответ 11

Мне нравятся решения, которые "элегантны для компьютеров". Мое решение здесь попадает в псевдонимы [вставлено] и [удалено] один раз каждый, чтобы получить их статусы и помещает результат в битовую переменную. Тогда каждая возможная комбинация INSERT, UPDATE и DELETE может быть легко протестирована на протяжении всего триггера с эффективными двоичными оценками (за исключением маловероятной комбинации INSERT или DELETE).

Это делает предположение, что не имеет значения, что такое оператор DML, если никакие строки не были изменены (что должно удовлетворять подавляющему большинству случаев). Таким образом, хотя это не так полно, как решение Roman Pekar, оно более эффективно.

При таком подходе у нас есть возможность запускать три слова "FOR INSERT, UPDATE, DELETE" для каждой таблицы, предоставляя нам A) полный контроль над порядком действий и b) одну реализацию кода за каждое действие, применимое к нескольким действиям. (Очевидно, что каждая модель реализации имеет свои плюсы и минусы, вам нужно будет оценивать свои системы отдельно для того, что действительно работает лучше всего.)

Обратите внимание, что операторы "exist (select * from" insert/deleted ") очень эффективны, поскольку нет доступа к диску ( 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

Ответ 12

Я нашел небольшую ошибку в Grahams в противном случае прохладно решение:

Это должно быть IF COLUMNS_UPDATED() < 0 - вставить или обновить
вместо > 0 вероятно, потому, что верхний бит интерпретируется как знак SIGNED integer sign... (?). Итак, всего:

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

Ответ 13

В первом сценарии я предположил, что в вашей таблице есть столбец IDENTITY

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

Во втором сценарии не нужно использовать столбец IDENTITTY

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

Ответ 14

Быстрое решение MySQL

Кстати: я использую MySQL PDO.

(1) В таблице автоматического приращения просто получите наивысшее значение (мой столбец name = id) из добавочного столбца после запуска каждого script:

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

(2) Запустите запрос MySQL, как вы обычно делали, и передайте результат в целое число, например:

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

(3) После того, как запрос INSERT INTO... ON DUPLICATE KEY UPDATE получит ваш последний вставленный идентификатор, например:

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

(4) Сравните и отреагируйте: если lastInsertId выше самого высокого в таблице, это, вероятно, INSERT, правильно? И наоборот.

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

Я знаю это быстро и, возможно, грязно. И это старый пост. Но, эй, я долго искал решение, и, может быть, кто-то все равно найдет мой способ. Все самое лучшее!

Ответ 15

Это делает трюк для меня:

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

Так как не все столбцы могут обновляться одновременно, вы можете проверить, обновляется ли какой-либо конкретный столбец примерно следующим образом:

IF UPDATE([column_name])

Ответ 16

простой способ

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 

Ответ 17

DECLARE @INSERTEDCOUNT INT,
        @DELETEDCOUNT INT

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

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

ЕСЛИ его обновление

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 1

если его вставка

 @INSERTEDCOUNT = 1
 @DELETEDCOUNT = 0

Ответ 18

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

Ответ 19

Я использовал эти exists (select * from inserted/deleted) запросы в течение длительного времени, но этого недостаточно для пустых операций CRUD (когда нет записей в таблицах inserted и deleted). Поэтому, немного исследовав эту тему, я нашел более точное решение:

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

Также можно использовать columns_updated() & power(2, column_id - 1) > 0, чтобы увидеть, обновлен ли столбец, но он небезопасен для таблиц с большим количеством столбцов. Я использовал несколько сложный способ вычисления (см. Полезную статью ниже).

Кроме того, этот подход будет по-прежнему некорректно классифицировать некоторые обновления как вставки (если каждый столбец в таблице влияет на обновление), и, вероятно, он будет классифицировать вставки, где в качестве удалений вставляются только значения по умолчанию, но они являются королями редких операции (в аренде в моей системе они есть). Кроме того, я не знаю, как улучшить это решение на данный момент.

• COLUMNS_UPDATED() для триггеров аудита Питера Родака
• Работа с очень большими битмашами Адама Мачанича