Inserir gatilho de atualização como determinar se insere ou atualiza

Eu preciso escrever um Insert, Update Trigger na tabela A, que irá apagar todas as linhas da tabela B, cuja coluna (dig, Desc) tem valores como o valor inserido / atualizado na coluna da tabela A (digamos, Col1). Como eu iria por aí escrevendo para que eu pudesse lidar com os casos de atualização e inserção. Como eu determinaria se o gatilho é executado para uma atualização ou inserção.

Se é o MS SQL Server …

Os gatilhos têm tabelas INSERTED e DELETED especiais para rastrear dados “antes” e “depois”. Então você pode usar algo como IF EXISTS (SELECT * FROM DELETED) para detectar uma atualização. Você só tem linhas em DELETED na atualização, mas sempre há linhas em INSERTED .

Procure por “inserido” no comando CREATE TRIGGER

Edit, 23 de novembro de 2011

Após comentar, esta resposta é apenas para gatilhos INSERTED e UPDATED .
Obviamente, os gatilhos DELETE não podem ter “sempre linhas no INSERTED ” como eu disse acima

 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 

Muitas dessas sugestões não levam em conta se você executar uma instrução de exclusão que exclua nada.
Digamos que você tente excluir onde um ID é igual a algum valor que não existe na tabela.
Seu gatilho ainda é chamado, mas não há nada nas tabelas Excluídas ou Inseridas.

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

Agradecimentos especiais a @KenDog e @Net_Prog por suas respostas.
Eu construí isso de seus scripts.

Eu estou usando o seguinte, ele também detecta corretamente instruções de exclusão que não excluem 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; 

Depois de muita pesquisa, não consegui encontrar um exemplo exato de um único triggersdor do SQL Server que manipule todas as três (3) condições das ações do triggersdor, INSERT, UPDATE e DELETE. Eu finalmente encontrei uma linha de texto que falava sobre o fato de que quando ocorre um DELETE ou UPDATE, a tabela comum DELETED conterá um registro para essas duas ações. Com base nessas informações, criei uma pequena rotina Action que determina por que o trigger foi ativado. Às vezes, esse tipo de interface é necessário quando há uma configuração comum e uma ação específica para ocorrer em um gatilho INSERT vs. UPDATE. Nestes casos, criar um gatilho separado para o UPDATE e o INSERT se tornaria um problema de manutenção. (ou seja, ambos os gatilhos foram atualizados corretamente para a correção do algoritmo de dados comum necessário?)

Para esse fim, gostaria de fornecer o seguinte snippet de código de evento de vários acionadores para manipular INSERT, UPDATE, DELETE em um acionador para um 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]=@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 

Eu acredito nested se um pouco confuso e:

Flat é melhor que nested [The 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 

Tente isso ..

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

Um possível problema com as duas soluções oferecidas é que, dependendo de como elas são gravadas, uma consulta de atualização pode atualizar registros zero e uma consulta de inserção pode inserir registros zero. Nesses casos, os conjuntos de registros inseridos e excluídos estarão vazios. Em muitos casos, se os conjuntos de registros Inseridos e Excluídos estiverem vazios, talvez você queira apenas sair do acionador sem fazer nada.

Isso pode ser um caminho mais rápido:

 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' 

Eu encontrei um pequeno erro em Grahams caso contrário legal solução:

Deve ser IF COLUMNS_UPDATED () < > 0 – inserir ou atualizar
em vez de> 0 provavelmente porque o bit superior é interpretado como bit de sinal de inteiro SIGNED … (?). Então, no 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 

Isso faz o truque para mim:

 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 nem todas as colunas podem ser atualizadas por vez, você pode verificar se uma determinada coluna está sendo atualizada por algo assim:

 IF UPDATE([column_name]) 
 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 

Eu gosto de soluções que são “ciência da computação elegante”. Minha solução aqui atinge os pseudotables [insert] e [deleted] uma vez em cada para obter seus status e coloca o resultado em uma variável de bit mapeado. Então, cada combinação possível de INSERT, UPDATE e DELETE pode ser prontamente testada em todo o trigger com avaliações binárias eficientes (exceto pela improvável combinação INSERT ou DELETE).

Ele faz a suposição de que não importa qual seja a instrução DML se nenhuma linha foi modificada (o que deve satisfazer a grande maioria dos casos). Portanto, embora não seja tão completo quanto a solução de Roman Pekar, é mais eficiente.

Com essa abordagem, temos a possibilidade de um acionador “FOR INSERT, UPDATE, DELETE” por tabela, o que nos dá: A) controle total sobre a ordem de ação eb) implementação de um código por ação aplicável de ação múltipla. (Obviamente, todo modelo de implementação tem seus prós e contras; você precisará avaliar seus sistemas individualmente para o que realmente funciona melhor.)

Observe que as instruções “exists (selecione * de« inserido / excluído ») são muito eficientes, pois não há access ao 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 

Embora eu também goste da resposta postada pelo @Alex, eu ofereço esta variação para a solução @ Graham’s acima

isso usa exclusivamente a existência de registros nas tabelas INSERTED e UPDATED, em vez de usar COLUMNS_UPDATED para o primeiro teste. Ele também fornece o alívio do programador paranoico, sabendo que o caso final foi 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 

você receberá NOOP com uma declaração como a seguinte:

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

Solução rápida MySQL

A propósito: estou usando o MySQL PDO.

(1) Em uma tabela de incremento automático, é só pegar o valor mais alto (meu nome de coluna = id) da coluna incrementada uma vez que todo script é executado primeiro:

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

(2) Execute a consulta do MySQL normalmente, e converta o resultado em inteiro, por exemplo:

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

(3) Após a consulta “INSERT INTO … ON DUPLICATE KEY UPDATE”, obtenha o último ID inserido da sua forma preferida, por exemplo:

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

(4) Compare e reaja: Se o lastInsertId for maior que o maior da tabela, provavelmente é um INSERT, certo? E vice versa.

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

Eu sei que é rápido e talvez sujo. E é um post antigo. Mas, ei, eu estava procurando por uma solução há muito tempo, e talvez alguém encontre o meu caminho de alguma forma útil. Muito bem sucedida!

maneira simples

 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 

No primeiro cenário eu supus que sua tabela tivesse a coluna 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 

No segundo cenário, não precisa usar a coluna 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 
 DECLARE @INSERTEDCOUNT INT, @DELETEDCOUNT INT SELECT @INSERTEDCOUNT = COUNT([YourColumnName]) FROM inserted SELECT @DELETEDCOUNT = COUNT([YourColumnName]) FROM deleted 

SE sua updation

  @INSERTEDCOUNT = 1 @DELETEDCOUNT = 1 

se a sua inserção

  @INSERTEDCOUNT = 1 @DELETEDCOUNT = 0 

Eu usei aquelas consultas exists (select * from inserted/deleted) por um longo tempo, mas ainda não é suficiente para operações CRUD vazias (quando não há registros em tabelas inserted e deleted ). Então, depois de pesquisar este tópico um pouco, encontrei uma solução mais 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' 

Também é possível usar columns_updated() & power(2, column_id - 1) > 0 para ver se a coluna está atualizada, mas não é seguro para tabelas com grande número de colunas. Eu usei uma maneira complexa de calcular (veja o artigo útil abaixo).

Além disso, essa abordagem ainda classificará incorretamente algumas atualizações como inserções (se todas as colunas da tabela forem afetadas pela atualização) e provavelmente classificará inserções onde somente valores padrão são inseridos como exclusões, mas esses são os principais de operações raras (em locação no meu sistema eles são). Além disso, não sei como melhorar essa solução no momento.

  • COLUMNS_UPDATED () para triggersdores de auditoria por Piotr Rodak
  • Lidando com muito grandes bitmasks por Adam Machanic