AUTOINCREMENTO SEM PERDER SEQUENCIA
Bom dia.
Tenho algumas tabelas, onde o ID tem que ser sequencial e não pode ter lacunas ou ser preenchido posteriormente com novos registros, tem que manter a sequencia correta de inserção e sem falhas na numeração.
Nesse cenário o campo autoincremento não serve. Pesquisando vi que é reprovável usar o MAX + 1, por diversos problemas de bloquear tabelas e impedir até consultas, em acessos concorrentes.
Então encontrei um código que usa Trigger e uma tabela de IDs para fazer isso para todas as tabelas nessa situação. O problema é que não entendi algumas coisas, e como não sei muito se MS SQL, gostaria da ajuda de vocês:
Nota: C# 2008 e SQL 2005 Express.
Código Encontrado:
[txt-color=#e80000]Perguntas:[/txt-color]
1- As partes em vermelho não entendi, tem meu comentário ao lado.
2- Ao usar uma transação no C#, farei alterações em várias tabelas simultaneamente, onde haverá a inclusão de um registro na tabela principal (pegando o ID pelo Trigger acima) e inclusão em outras tabelas auxiliares usando esse ID para relacionamento. A dúvida é, se ocorrer um erro onde a transação terá um Rollback, como ficará essa numeração [Ô]INCREMENTADA[Ô] pelo trigger, uma vez que tem um UPDATE nele para o Valor = Valor + 1. O Rollback irá reverter isso também?
Tenho algumas tabelas, onde o ID tem que ser sequencial e não pode ter lacunas ou ser preenchido posteriormente com novos registros, tem que manter a sequencia correta de inserção e sem falhas na numeração.
Nesse cenário o campo autoincremento não serve. Pesquisando vi que é reprovável usar o MAX + 1, por diversos problemas de bloquear tabelas e impedir até consultas, em acessos concorrentes.
Então encontrei um código que usa Trigger e uma tabela de IDs para fazer isso para todas as tabelas nessa situação. O problema é que não entendi algumas coisas, e como não sei muito se MS SQL, gostaria da ajuda de vocês:
Nota: C# 2008 e SQL 2005 Express.
Código Encontrado:
-- Cria a tabela de pessoas
CREATE TABLE tblPessoas (IDPessoa INT NOT NULL, NomePessoa VARCHAR(50))
ALTER TABLE tblPessoas ADD CONSTRAINT PKPessoa PRIMARY KEY (IDPessoa)
-- Cria a tabela de contadores (IDs)
CREATE TABLE tblContadores (NomeTabela VARCHAR(20), Valor INT)
INSERT INTO tblContadores VALUES ([ô]tblPessoas[ô],0)
-- Cria a trigger de inserção
CREATE TRIGGER tblSeqPessoa ON tblPessoas
INSTEAD OF INSERT
[txt-color=#e80000]AS BEGIN[/txt-color] //Não entendi isso é parte da Trigger ou relacionado a parte de baixo?
-- Captura o ID atual (bloqueando-o para que duas pessoas não peguem o mesmo ID)
DECLARE @IDPessoa INT
SET @IDPessoa = (SELECT VALOR FROM tblContadores (XLOCK) WHERE NomeTabela = [ô]tblPessoas[ô])
SET @IDPessoa = @IDPessoa + 1
-- Atualiza o contador
UPDATE tblContadores SET Valor = Valor + 1 WHERE NomeTabela = [ô]tblPessoas[ô]
-- Insere o registro
INSERT INTO tblPessoas (IDPessoa, NomePessoa)
SELECT @IDPessoa AS IDPessoa, NomePessoa FROM INSERTED
[txt-color=#e80000]END[/txt-color] // Esse end finaliza o que? Onde foi seu inÃcio no AS BEGIN ?
// Essa parte và que são as ações do programa para testar a estrutura das tabelas no banco
-- Simula uma inserção
INSERT INTO tblPessoas (NomePessoa) VALUES ([ô]Nome 1[ô])
-- Verifica o registro
SELECT IDPessoa, NomePessoa FROM tblPessoas
-- Verifica o contador
SELECT NomeTabela, Valor FROM tblContadores[txt-color=#e80000]Perguntas:[/txt-color]
1- As partes em vermelho não entendi, tem meu comentário ao lado.
2- Ao usar uma transação no C#, farei alterações em várias tabelas simultaneamente, onde haverá a inclusão de um registro na tabela principal (pegando o ID pelo Trigger acima) e inclusão em outras tabelas auxiliares usando esse ID para relacionamento. A dúvida é, se ocorrer um erro onde a transação terá um Rollback, como ficará essa numeração [Ô]INCREMENTADA[Ô] pelo trigger, uma vez que tem um UPDATE nele para o Valor = Valor + 1. O Rollback irá reverter isso também?
O melhor truque para seu caso é:
Para fazer a inserção no próximo Id disponÃvel, você antes precisa saber se existe uma lacuna. Para isso, você faz assim:
Conte quantos registros tem na tabela. Digamos que tenham sido 10. 10 registros, seriam 1,2,3,4,5,6...10, certo? Então, a soma dos números de um à dez é 55, logo, se essa soma não der 55, então tem alguma lacuna no meio. Para saber qual o que falta, subtraia o valor da soma dos id[ô]s de 55, por exemplo, a soma dos registros deu 49, subtraindo 49 de 55, sobra 6, logo o ID que falta é o 6, então insira o próximo usando o 6. Parace um pouco complicado, mas são apenas duas operações de banco bem leves: A contagem de registros e a soma dos id[ô]s
Para fazer a inserção no próximo Id disponÃvel, você antes precisa saber se existe uma lacuna. Para isso, você faz assim:
Conte quantos registros tem na tabela. Digamos que tenham sido 10. 10 registros, seriam 1,2,3,4,5,6...10, certo? Então, a soma dos números de um à dez é 55, logo, se essa soma não der 55, então tem alguma lacuna no meio. Para saber qual o que falta, subtraia o valor da soma dos id[ô]s de 55, por exemplo, a soma dos registros deu 49, subtraindo 49 de 55, sobra 6, logo o ID que falta é o 6, então insira o próximo usando o 6. Parace um pouco complicado, mas são apenas duas operações de banco bem leves: A contagem de registros e a soma dos id[ô]s
Kerplunk não é isso que eu preciso, pois como expliquei não posso usar em alguns caso o AutoIncremento para exatamente não gerar lacunas, uma vez que também não posso prrencher essas lacunas com outros registros. O que quero dizer é que obrigatoriamente a numeração tem que ser sequencial e um registro com data de hoje, não pode ser gravado com numeração anterior ao de ontem para preencher uma lacuna como sugeriu.
No sistema que tenho com MS Access uso o MAX + 1, mas nessa migração para MS SQL Server, isso poderá dar muitos problemas. Por isso achei esse código, mas estou querendo entender algumas coisas nele.
Como perguntei sobre o AS BEGIN e se tem relação com o END mais abaixo e se uma transação irá também reverter o Update to Trigger que incrementa a tabala de Ãndices dessa técnica sugerida no código?
No sistema que tenho com MS Access uso o MAX + 1, mas nessa migração para MS SQL Server, isso poderá dar muitos problemas. Por isso achei esse código, mas estou querendo entender algumas coisas nele.
Como perguntei sobre o AS BEGIN e se tem relação com o END mais abaixo e se uma transação irá também reverter o Update to Trigger que incrementa a tabala de Ãndices dessa técnica sugerida no código?
BEGIN....END tem o mesmo papel das chaves no C# {.....} e não é [Ô]junto[Ô] AS e BEGIN são coisas separadas, entenda mais ou menos assim, traduzindo a procedure para [Ô]português[Ô]:
Crie um gatilho na tabela tblPessoas (Create trigger...)
Ao invés de insert (instead of insert)
AS (execute o seguinte bloco)
BEGIN (inicio do bloco)
...
...
...
END (fim do bloco)
Essa procedure acima, nada mais é que inserção de modo normal, nada que você não faria exatamente igual usando blocos de código e SQL. Mas acho que entendi o que você quer e se entendi, a saÃda é usar o MAX + 1 mesmo
Crie um gatilho na tabela tblPessoas (Create trigger...)
Ao invés de insert (instead of insert)
AS (execute o seguinte bloco)
BEGIN (inicio do bloco)
...
...
...
END (fim do bloco)
Essa procedure acima, nada mais é que inserção de modo normal, nada que você não faria exatamente igual usando blocos de código e SQL. Mas acho que entendi o que você quer e se entendi, a saÃda é usar o MAX + 1 mesmo
Entendi, então achei esse outro que faz isso, mas aqui tem outras dúvidas também:
1- a parte do código abaixo está tudo [Ô]junto[Ô] e faz parte do AS ou são coisas separadas?
declare @cod int,@tel int
select @cod = case when max(cod_pac) is null then 1 else max(cod_pac)+1 end from tel_pac(xlock)
select @tel=i.tel from inserted i
insert into tel_pac(cod_pac,tel) values (@cod,@tel)
2- Nessa linha abaixo, o que é o (i) e o inserted ?
select @tel=i.tel from inserted i
3- A última linha faz parte do código que irá gerar o ID ou é um exemplo de uso da inserção?
insert into tel_pac(cod_pac,tel) values (@cod,@tel)
4- Fazendo como está aqui, a mesma dúvida do anterior, se eu cancelar uma transação essa trigger irá anular a geração do último ID? Pergunto porque pelos exemplos, isso é feito pelo Banco e não pelo código C# da aplicação, então não sei qual o resultado nesse caso.
create table tel_pac
(cod_pac int not null,
tel int not null)
go
alter table tel_pac add constraint pk_codpac primary key(cod_pac)
go
create trigger auto_num
on tel_pac
instead of insert
as
declare @cod int,@tel int
select @cod = case when max(cod_pac) is null then 1 else max(cod_pac)+1 end from tel_pac(xlock)
select @tel=i.tel from inserted i
insert into tel_pac(cod_pac,tel) values (@cod,@tel) 1- a parte do código abaixo está tudo [Ô]junto[Ô] e faz parte do AS ou são coisas separadas?
declare @cod int,@tel int
select @cod = case when max(cod_pac) is null then 1 else max(cod_pac)+1 end from tel_pac(xlock)
select @tel=i.tel from inserted i
insert into tel_pac(cod_pac,tel) values (@cod,@tel)
2- Nessa linha abaixo, o que é o (i) e o inserted ?
select @tel=i.tel from inserted i
3- A última linha faz parte do código que irá gerar o ID ou é um exemplo de uso da inserção?
insert into tel_pac(cod_pac,tel) values (@cod,@tel)
4- Fazendo como está aqui, a mesma dúvida do anterior, se eu cancelar uma transação essa trigger irá anular a geração do último ID? Pergunto porque pelos exemplos, isso é feito pelo Banco e não pelo código C# da aplicação, então não sei qual o resultado nesse caso.
1 - São coisas diferentes, abaixo do [Ô]as[Ô] está o código a ser executado pela trigger
2 - [Ô]Inserted[Ô] e [Ô]Deleted[Ô] são pseudo-tabelas que retorna o último registro inserido na tabela atual, no caso, o registro que disparou a trigger. [Ô]i[Ô] é apenas um alias para isso.
3 - Ela faz parte da trigger
4 - Se você iniciar uma transação na conexão em que for executar a alteração na tabela que vai disparar a trigger, essa transação será respeitada pela trigger, incluindo para commit e rollback.
2 - [Ô]Inserted[Ô] e [Ô]Deleted[Ô] são pseudo-tabelas que retorna o último registro inserido na tabela atual, no caso, o registro que disparou a trigger. [Ô]i[Ô] é apenas um alias para isso.
3 - Ela faz parte da trigger
4 - Se você iniciar uma transação na conexão em que for executar a alteração na tabela que vai disparar a trigger, essa transação será respeitada pela trigger, incluindo para commit e rollback.
Luis,
A muitos anos utilizo algo quase igual ao Max, Bco Mysql, utilizo uma tabela só para gerenciar os numeros de registros, exemplo das Nfe[ô]s.
Tabela_Add. Nela é colocado o numero da proxima nota, então antes da inserção, é feito uma consulta nessa tabela para saber qual o nº da proxima nota, ai que entra o perigo de sistema em redes, um outro usuario pode pegar o mesmo numero, então a tabela que recebe esse numero tem o campo ID, por exemplo, ele é chave primaria, então quando da inserção, se esse numero já estiver inserido, ira gerar o erro:[Ô]Duplicate entry[Ô]
Então o sistema faz um looping, acrescendo +1. até que o erro acabe.
Ao final da inserção, ai sim se atualiza Tabela_Add=Tabela_Add+1.
Nunca mais tive problemas, e em sistemas de fluxo, medio a alto.
A muitos anos utilizo algo quase igual ao Max, Bco Mysql, utilizo uma tabela só para gerenciar os numeros de registros, exemplo das Nfe[ô]s.
Tabela_Add. Nela é colocado o numero da proxima nota, então antes da inserção, é feito uma consulta nessa tabela para saber qual o nº da proxima nota, ai que entra o perigo de sistema em redes, um outro usuario pode pegar o mesmo numero, então a tabela que recebe esse numero tem o campo ID, por exemplo, ele é chave primaria, então quando da inserção, se esse numero já estiver inserido, ira gerar o erro:[Ô]Duplicate entry[Ô]
Então o sistema faz um looping, acrescendo +1. até que o erro acabe.
Ao final da inserção, ai sim se atualiza Tabela_Add=Tabela_Add+1.
Nunca mais tive problemas, e em sistemas de fluxo, medio a alto.
no caso do identity ele segue uma sequencia exata ao menos que entre no bloco catch da procedure dai ele usa o proximo nº
mais no caso se for deletado algum registro anterior ai sai da sequecia numerica ñ? o proprio SGBD faz isso, se não fizer dai é um GA, e qual seria a funcionalidade?
mais no caso se for deletado algum registro anterior ai sai da sequecia numerica ñ? o proprio SGBD faz isso, se não fizer dai é um GA, e qual seria a funcionalidade?
Pelo pouco que conheço, campo AutoIncrement não deixa lacunas.
Porém, se o sistema permite Exclusão de registros, aÃ, elas aparecerão.
A menos que seja tão importante que você tenha que ao invés de excluir, ZERAR todas as informações e marcar uma flag como EXCLUIDO. Mas, há coerência?
Qualquer que seja o método para gerar a pk, se há exclusão, haverá brechas.
Porém, se o sistema permite Exclusão de registros, aÃ, elas aparecerão.
A menos que seja tão importante que você tenha que ao invés de excluir, ZERAR todas as informações e marcar uma flag como EXCLUIDO. Mas, há coerência?
Qualquer que seja o método para gerar a pk, se há exclusão, haverá brechas.
Pessoal o problema não é a exclusão, pois nessas tabelas [Ô]CrÃticas[Ô] não existe exclusão de dados. O problema é que usando autoincremento, se der erro na inserção o MS SQL perde o número, ou seja, ele incrementa mas não salva o registro em função do Rollback gerado pelo erro na hora de gravar. Por isso o autoincremento não serve nestas tabelas especÃficas.
Nilsontres eu também tenho um sistema assim com VB6 e MSAccess e funciona perfeitamente, pois tudo é feito via código no VB. Agora estou migrando para C# com SQL Server e por isso estou tentando aprender como fazer com a novas linguagens.
Terei ao menos umas 10 tabelas nessa situação que terei de controlar a numeração para não ter a perda do ID na hora do Insert. Li algo sobre no SQL Server ter uma tabela auxiliar só para guardar o nome das tabelas crÃticas e um ID para elas, ao incluir um registro, verificava nessa tabela o nome da tabela de origem e seu ID para gravar, nesse caso havia um bloqueio da tabela para não ser criado um ID semelhante enquanto todo o processo de gravação não fosse concluido, mas como não conheço SQL tão bem para fazer isso, não entendi exatamente como seria isso.
Você poderia passar esse seu código?
Nilsontres eu também tenho um sistema assim com VB6 e MSAccess e funciona perfeitamente, pois tudo é feito via código no VB. Agora estou migrando para C# com SQL Server e por isso estou tentando aprender como fazer com a novas linguagens.
Terei ao menos umas 10 tabelas nessa situação que terei de controlar a numeração para não ter a perda do ID na hora do Insert. Li algo sobre no SQL Server ter uma tabela auxiliar só para guardar o nome das tabelas crÃticas e um ID para elas, ao incluir um registro, verificava nessa tabela o nome da tabela de origem e seu ID para gravar, nesse caso havia um bloqueio da tabela para não ser criado um ID semelhante enquanto todo o processo de gravação não fosse concluido, mas como não conheço SQL tão bem para fazer isso, não entendi exatamente como seria isso.
Você poderia passar esse seu código?
Citação:O problema é que usando autoincremento, se der erro na inserção o MS SQL perde o número, ou seja, ele incrementa mas não salva o registro em função do Rollback gerado pelo erro na hora de gravar. Por isso o autoincremento não serve nestas tabelas especÃficas.
é isso mesmo.
Citação:Nilsontres eu também tenho um sistema assim com VB6 e MSAccess e funciona perfeitamente, pois tudo é feito via código no VB. Agora estou migrando para C# com SQL Server e por isso estou tentando aprender como fazer com a novas linguagens.
Mas a linguagem que vc utiliza não altera, eu faço isso em MSAccess com vb6 e .net com MYSQL.
Se vc Utiliza Transações, o que eu acho imprescindÃvel para quem quer um sistema seguro. Obs: Incrivel que tem muita gente que nem sabe o que é isso, imagine o risco que corre um sistema sem transações.
Voltando la, com transações, que eu saiba não tem como evitar essa lacuna utilizando Auto Incremento.
Tópico encerrado , respostas não são mais permitidas