Executar um update sem where é uma das situações mais desagradáveis que pode acontecer na área de tecnologia, então o assunto de hoje é extremamente relevante: Como EVITAR UPDATE sem WHERE.
Se você já fez o treinamento de “Transact-SQL” para desenvolvedores, disponível em meu site, aprendeu meus 3 segredos para garantir que essa situação desastrosa nunca ocorra com você, porém nem todos que trabalham com o banco que você cuida fizeram o treinamento.
Por essa razão, hoje veremos uma forma de você definitivamente evitar update sem where nos bancos que você administra.
Como EVITAR UPDATE sem WHERE
Para impedir que desavisados façam update sem where em nosso banco usaremos triggers e a propriedade contexto_info. Abaixo o script da aula de hoje para você testar e usar como base na sua implementação:
-------------------------------------------------------------------------------
-- Como evitar UPDATE sem WHERE
-------------------------------------------------------------------------------
--------------------------------------------------------
-- Requisitos para nosso teste
--------------------------------------------------------
-- Banco de testes
use master
if db_id('curso') is not null
drop database curso
go
create database curso
go
use curso
go
-- cria a tabela amigos
if object_id('amigos') is not null drop table amigos
create table amigos (id int primary key, nome varchar(100), email varchar(100), dt_nascimento date)
insert into amigos values (1, 'Zézão', '[email protected]', '1990-01-02')
insert into amigos values (2, 'Zezinho', '[email protected]', '1990-02-01')
insert into amigos values (3, 'Abismail', '[email protected]', '1991-03-04')
insert into amigos values (4, 'Lazarildo', '[email protected]', '1991-03-04')
insert into amigos values (5, 'Febril', '[email protected]', '1992-03-04')
insert into amigos values (6, 'Falsiane', '[email protected]', null)
go
--------------------------------------------------------
-- O que ocorre em geral :S
--------------------------------------------------------
-- Consultar amigos
select * from amigos
-- Atualizar a data de aniversário do amigo...
update amigos set dt_nascimento = '1980-02-01'
--------------------------------------------------------
-- SOLUÇÃO 1: Trigger para impedir rowcount = count(*)
--------------------------------------------------------
-- Trigger para impedir
if object_id('tr_amigos_impede_sem_where') is not null drop trigger tr_amigos_impede_sem_where
go
create trigger tr_amigos_impede_sem_where on amigos for update, delete
as begin
declare @count int = @@rowcount
if @count = (select sum(row_count) from sys.dm_db_partition_stats where object_id = object_id('amigos') and index_id in (0,1))
begin
raiserror('ATENÇÃO: Não é permitido atualizar todos os registros de uma só vez. A transação foi cancelada.', 16, 1)
rollback transaction
end
end
go
-- TR permite somente update "COM where":
update amigos set dt_nascimento = '1980-02-01' where id = 6
update amigos set dt_nascimento = '1980-02-01'
select * from amigos
go
-- Permitir update sem where temporário para ADMINS
alter table amigos disable trigger all
update amigos set dt_nascimento = '1980-02-01'
alter table amigos enable trigger all
go
--------------------------------------------------------
-- SOLUÇÃO 2: Permitir sem where via codigo da TR
--------------------------------------------------------
if object_id('tr_amigos_impede_sem_where') is not null drop trigger tr_amigos_impede_sem_where
go
create trigger tr_amigos_impede_sem_where on amigos WITH ENCRYPTION for update, delete
as begin
declare @count int = @@rowcount
if context_info() = 0x1234567890
begin
set context_info 0x -- reinicia o context_info para permitir apenas 1 execução sem where
print 'Contexto admin, verificação de update/delete sem where não realizada.'
end
else
if @count = (select sum(row_count) from sys.dm_db_partition_stats where object_id = object_id('amigos') and index_id in (0,1))
begin
raiserror('ATENÇÃO: Não é permitido atualizar todos os registros de uma só vez. A transação foi cancelada.', 16, 1)
rollback transaction
end
end
-- TR é desativada no "context_info administrativo":
select context_info from sys.dm_exec_sessions where session_id = @@spid;
set context_info 0x1234567890;
update amigos set dt_nascimento = '1980-02-01' where id = 6
update amigos set dt_nascimento = '1980-02-01' where id between 1 and 3
update amigos set dt_nascimento = '1980-02-01'
select * from amigos
go
Se você quiser, pode inclusive melhorar essa trigger para que ela registre em um log as tentativas de update sem where. Se esse for o caso, consulte o meu artigo “Como descobrir quem mexeu no seu SQL” para entender melhor como você pode registrar informações em um log usando uma trigger. Somando o conhecimento de hoje com o do artigo que indiquei você terá a base necessária para fazer essa implementação com maestria.
CONCLUSÃO
Update sem where só é engraçado em memes da internet…
Brincadeiras a parte, espero muito que você que acompanha o blog e já fez meus treinamentos NUNCA passe por uma dessas. Alias, em poucas ocasiões podemos de verdade usar as palavras NUNCA e/ou SEMPRE, mas acho que para esse caso é apropriado:
– NUNCA deixe de usar as técnicas que lhe ensinei no curso evitar update sem where;
– SEMPRE preocupe-se em evitar que outros façam update sem where nos bancos que você cuida.
Oi, aqui é o Josué. Sou o criador do site dba-pro.com.
Minha missão é ajudar pessoas a usarem o potencial do SQL Server em suas profissões, para que trabalhem felizes e bem remuneradas.
Sou consultor e empreendedor, atuando em empresas desde 2000 e dando aulas desde 2004. Adoro SQL, Excel, automatização de processos, finanças e Coaching. Quando não estou trabalhando ou estudando, gosto de leitura, games para PC, filmes, séries e conversas estimulantes.
