Retaining SubSequence Integrity

[CassidyHunt]

Is there a way to maintain a subsequence in a table? For example:

ID      MaterialID    Description   NoteID    Note
-----------------------------------------------------------
1       123           Test123       1         this is a test
2       123           Another ie    2         this is good
3       123           test
4       123           try           3         adsfasdfasdf
5       124           123133213     1         asdfasdfasd
6       124           asdfasdfasd   2         asdfasdfasfda
7       125           asdfasfasdf

There are three things I want to see. I want to be able to only increment the noteid if a note is present. Second I want to keep the noteid sequential only to the related material id. Finally I would like to be able to reorder the noteid's if a note is added later on. For example if a note is added to row 3 I would like to reorder it so 3 would appear on row 3 and 4 would appear on row 4.

Hopefully this is clear. I am thinking this can be done with an insert and update trigger but I am unsure as to how to approach a sub sequence in a trigger.

Thanks

Cassidy

 

[SimonSellick]

Cassidy,

How do you know which row is which within a material id - is it in id column sequence? Can you add notes with an UPDATE or only an INSERT? Why do notes need to be numbered - are they really another entity (in which case perhaps they should have their own table)?

Anyway, I think that the answer to your question is 'yes' - if you can order the subset of rows then you can also number them, either by mapping the columns that define the sequence (e.g. id) to a numeric value (e.g. id - min(id) + 1 grouped by the subset), or by counting the returned rows. In your case the subset should contain only those rows with a note, so rownum (from a sub-query) might be the better sequence generator.

Hope this helps.

Simon.

 

[CassidyHunt]

Basically the end result is a material specification where it details out a list of elements used to make a specific metal. Each element may or may not have a note associated with it. Notes however have to be provided in numerical sequence to the appearance of the element on the specification. So if someone goes back in later on and adds a note to any of the elements everything after that element with a note has to be renumbered.

Material ID is from the materials table I have and is a foreign key relationship. I can not see giving notes their own table because they are still just a detail of the element and do not really have, for lack of a better term, enough weight to support a reason to provide a table with another foreign key primary key relationship.

What I was thinking was creating a function that would use execute immediate and pass it the table name and material id then update the number sequence after every insert or update. I am thinking this would need to be called from a trigger. That is where I am really weak and lack confidence in deciding if this is the best route. In addition I was thinking of creating a sequence in oracle that resets after every use but I do not know how to do that either.

Let me know if this makes sense. I am not looking for anyone to produce my final result. I am looking more at an example that does what I need then I can fit it to my solution.

Thanks

Cassidy