SQL-TRIGGER MAKING AUTO NUMBERING WRONG

Asked

Viewed 46 times

-1

Hello, I’m studying sql for very little time so I’m sorry if any nomenclature is not ok.

I’ve created some triggers for my test database and in some cases it’s working perfectly. In others however it is creating wrong sequences. I’ve got the client chart for example. Where I created the table and created a sequence

CREATE SEQUENCE SEQ_CLI START WITH 1 INCREMENT BY 1 NOMAXVALUE;

After I created Trigger

CREATE TRIGGER "TRG_CLIENTE1" BEFORE INSERT ON "CLIENT" FOR EACH ROW BEGIN SELECT "SEQ_CLI". NEXTVAL INTO:NEW.COD_CLI FROM DUAL; END;

Now doing INSERT in the client table the first generated COD_CLI is 16, and has nothing inserted before that. If anyone can help you with anything that might be happening, I’d appreciate it. Note: I did all the triggers and sequences in the same way.

inserir a descrição da imagem aqui

sql trigger sql-insert

1 answer

0


A sequence, like auto increment, purposely skips the values if they have been deleted somewhere in time. For example:

Criou o registro 13
Criou o registro 14
Deletou o registro 14
Criou o registro 15

Note that even when deleting the record with ID 14, the next one to be created will not be 14, but 15.

The concept of ID is to be unique. It can generate numerous problems if you reuse an ID even if it has been deleted.

  • Okay, I get the point, you’ll be skipping used Ids. In this case no previous records were deleted, the first record released started in ID 16. But in my instruction I had done to start in 1. Can you tell me what might have gone wrong? I appreciate the attention.

  • You are sharing this same Quence (CREATE SEQUENCE) in other tables?

  • I did not use it in other tables, but when I was inserting I made some attempts until I was able to insert, because something was missing, commas, parentheses etc., that must have been it. Thank you.

  • Also note that Sequences guarantee uniqueness and not integrity , so the "ids" can lose sequence, some applications (Invoice , No Apocices etc) do not allow this and another solution must be made.

Browser other questions tagged sql trigger sql-insert

You are not signed in. Login or sign up in order to post.