Olá pessoal,
No post de hoje vamos falar sobre o ERRO abaixo ao utilizar uma Trigger:
“Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.”
Trigger:
Vamos começar o nosso exemplo criando uma tabela chamada “Teste_Trigger” e inserindo alguns registros.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE Traces GO DROP TABLE IF EXISTS Teste_Trigger GO CREATE TABLE Teste_Trigger ( ID INT IDENTITY(1,1), Nome VARCHAR(100), Dt_Nascimento DATE ) GO INSERT INTO Teste_Trigger (Nome, Dt_Nascimento) VALUES ('Luiz Lima', '19850215'), ('Fabricio Lima', '19800624'), ('Dirceu Lima', '19601005') SELECT * FROM Teste_Trigger GO |
Depois disso, criamos uma Trigger do tipo “AFTER UPDATE”, ou seja, a trigger será executada sempre APÓS um UPDATE na tabela “Teste_Trigger”.
Além disso, criamos também uma segunda tabela chamada “Teste_Trigger_Audit”. Após um update na tabela “Teste_Trigger”, a trigger será acionada e irá inserir os registros que foram alterados na tabela “Teste_Trigger_Audit”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
DROP TABLE IF EXISTS Teste_Trigger_Audit GO CREATE TABLE Teste_Trigger_Audit ( Id_Audit INT IDENTITY(1,1) NOT NULL, ID INT NOT NULL, Nome VARCHAR(100) NOT NULL, Dt_Nascimento DATE ) GO CREATE TRIGGER trgUpdate_Teste_Trigger ON Teste_Trigger AFTER UPDATE AS BEGIN INSERT INTO Teste_Trigger_Audit (ID, Nome, Dt_Nascimento) SELECT ID, Nome, Dt_Nascimento FROM inserted END GO |
Trigger – Exemplos:
Então vamos fazer alguns testes para ver o funcionamento da Trigger na prática!
Primeiro vamos executar um UPDATE em apenas uma linha com o comando abaixo:
|
1 2 3 |
UPDATE Teste_Trigger SET Dt_Nascimento = '1989-12-02' WHERE ID = 1 |
Repare que ao executar o comando, ele irá retornar o seguinte:
(1 row affected)
(1 row affected)
Ou seja, ele fez o UPDATE na tabela “Teste_Trigger”, acionou a trigger e também fez o INSERT na tabela “Teste_Trigger_Audit”.
|
1 2 3 |
SELECT * FROM Teste_Trigger SELECT * FROM Teste_Trigger_Audit |
Vamos ver se você realmente entendeu e agora vamos fazer um “UPDATE SEM WHERE” para atualizar todos os registros da tabela.
|
1 2 |
UPDATE Teste_Trigger SET Dt_Nascimento = '1980-06-29' |
Repare novamente no resultado, dessa vez ele fez o UPDATE nos três registros da tabela “Teste_Trigger” e depois também inseriu os três registros alterados na tabela “Teste_Trigger_Audit”.
(3 rows affected)
(3 rows affected)
Vejamos o resultado:
|
1 2 3 |
SELECT * FROM Teste_Trigger SELECT * FROM Teste_Trigger_Audit |
Observação – Tabelas “inserted” e “deleted”:
Não sei se você reparou em um detalhe, mas dentro da Trigger eu utilizo a tabela “inserted”, que na verdade contém os “novos registros” APÓS o UPDATE.
Eu também poderia utilizar a tabela “deleted”, e nesse caso teríamos os “registros antigos” ANTES do UPDATE.
Caso do Dia a Dia – Problema Real:
Finalmente vamos para o nosso Caso do Dia a Dia!
Agora vou simular o meu problema real e adicionar a coluna “Ds_Observacao” do tipo “TEXT” na tabela “Teste_Trigger”.
|
1 2 3 4 |
ALTER TABLE Teste_Trigger ADD Ds_Observacao TEXT SELECT * FROM Teste_Trigger |
Feito isso, vamos incluir a nova coluna “Ds_Observacao” na Trigger:
|
1 2 3 4 5 6 7 8 9 10 11 |
GO ALTER TRIGGER trgUpdate_Teste_Trigger ON Teste_Trigger AFTER UPDATE AS BEGIN INSERT INTO Teste_Trigger_Audit (ID, Nome, Dt_Nascimento, Ds_Observacao) SELECT ID, Nome, Dt_Nascimento, Ds_Observacao FROM inserted END GO |
E para a minha surpresa, acabei descobrindo na prática que as Triggers possuem algumas limitações quanto aos tipos de dados das colunas que são utilizadas nas tabelas “inserted” ou “deleted”.
Msg 311, Level 16, State 1, Procedure trgUpdate_Teste_Trigger, Line 6 [Batch Start Line 81]
Cannot use text, ntext, or image columns in the ‘inserted’ and ‘deleted’ tables.
Msg 207, Level 16, State 1, Procedure trgUpdate_Teste_Trigger, Line 5 [Batch Start Line 81]
Invalid column name ‘Ds_Observacao’.
OBS: O link abaixo da documentação oficial da Microsoft contém mais informações sobre as triggers e também sobre suas limitações.
Para finalizar, nesse caso você deve desconsiderar a coluna com o “tipo inválido” dentro da trigger ou tentar transformar ela em algum “tipo válido”.
Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!
Me siga no LinkedIn e YouTube para ficar por dentro das novidades.
Abraço,
Luiz Vitor França Lima
Consultor SQL Server