MS SQL Trigger insertion with continuous serial between passed values 1

[sam4help]

Dear All
I am new to triggers usage and this first time I am in need to write a trigger (I am developing a application in VB.NET 2010 and MS SQL Express 2008 R2). Would really appreciate if some one can help and guide in this.
My need:
I have two tables

Now, I insert a row through user in main table they input the range let’s say 1001 to 2000 so, now i need to auto insert records each serial in second table in that case will be 1001..1002..1003.....1999.. 2000 so total in this case 1000 records. ioID will be the binding unique key in both tables.

Thanking you in advance for kind help and guidance,

Best Regards,

Sam

 

[gmmastros]

This may be trickier than you thought.

Have you considered what should happen if the data in the main table is updated to have a different range of values? Should the rows in the 2nd table be added/deleted to maintain the proper list?

Have you considered what should happen if the entire row in the main table is deleted? Should this deleted all the rows in the 2nd table?

I mention this because you are likely to need 3 separate triggers, one for insert, another for update, and a 3rd for delete.

Creating a row for each value is not terribly difficult (if you know how), but doing this quickly requires some careful consideration. Do you have a numbers table in your database? If not, would you consider adding one? What is the typical range of values, and what would be an extreme range of values (for example, would there ever be a range that represents millions of rows)?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Thanks George for spending sometime on this.

Have you considered what should happen if the data in the main table is updated to have a different range of values? Should the rows in the 2nd table be added/deleted to maintain the proper list?

Will not be allowing to update, they will have to delete the old Master Entry

Have you considered what should happen if the entire row in the main table is deleted? Should this deleted all the rows in the 2nd table?

Yes, the old entries will have to be deleted.

I mention this because you are likely to need 3 separate triggers, one for insert, another for update, and a 3rd for delete.

I plan to delete this entries with simple delete query, if the Master entries are deleted.

Creating a row for each value is not terribly difficult (if you know how), but doing this quickly requires some careful consideration. Do you have a numbers table in your database? If not, would you consider adding one? What is the typical range of values, and what would be an extreme range of values (for example, would there ever be a range that represents millions of rows)?

Actually this range may vary. The scenario is each employee gets a book of sheets which has a serial no. and each sheet is accounted for whenever whenever in use employee will have to update the transaction table with that details against the sheet.
So, if any sheet used in a batch the delete option will not be available. Here batch no is nothing but the unique binding no in both table that is ioID.

Hope above explanation helps in understanding the environment,

Best Regards,

Sam

 

[gmmastros]

Do you have a numbers table in your database?

The numbers table I have in mine. Table name = Numbers. 1 column named Num. There is a row for each "number", starting at 1 and going to 1,000,000. It takes approximately 1 megabyte to store this table in the DB.

You can create a numbers table like this:

Create Table Numbers(Num Int Identity(1,1), Primary Key (Num))

Then you can fill it with 10,000 values like this:

Set NOCOUNT ON
Insert Into Numbers Default Values
Go 10000

The line that is "Go [!]10000[/!]" may need to be altered to accommodate the largest ioDocumentEndSerialNo that you expect to have. Make it larger than you think you'll need.

You can then use it like this....

CREATE TRIGGER dbo.tgr_DocumentRange
  On dbo.ioMaster
  For Insert
As
Begin
  Set NoCount On

  Insert
  Into   ioTransactions(ioId, ioSerialNo)
  Select I.ioId, Numbers.Num
  From   Inserted I
         Inner Join Numbers
           On Numbers.Num Between ioDocumentStartSerialNo And ioDocumentEndSerialNo
End

I did not test this code, so there may be some minor problems with it, but I think you get the idea. If not, let me know and I will explain it.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Appreciate your kind help;
Sorry George, I feel I missed one point here see there may be Repetition of numbers as their is a Prefix so its not always a serial to start form and end. So I just need to add a serial no between the Master Start and Master End Serial No Input.

SO something like if Master Entry says

[b]Prefix :[/b] [COLOR=#CC0000]C[/color]

Start Serial No : [COLOR=#CC0000]1001[/color]

End Serial No : [COLOR=#CC0000]2000[/color]

Next Time it may be

[b]Prefix :[/b] [COLOR=#CC0000]D[/color]

Start Serial No : [COLOR=#CC0000]1501[/color]

End Serial No : [COLOR=#CC0000]3000[/color]

So, serial doesn't matter here its just the range has to be updated.

Best Regards,

Sam

 

[gmmastros]

Now I am a little confused. Sorry.

Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Something like this

Best Regards,

Sam

 

[gmmastros]

Question: When the second entry is made to the master table, do you want the rows deleted from the other table and then re-added for the new prefix, or should the second table now have entries for prefix d and prefix c. In your example, would there be 15 rows in the other table after adding the second entry?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Question: When the second entry is made to the master table, do you want the rows deleted from the other table and then re-added for the new prefix, or should the second table now have entries for prefix d and prefix c. In your example, would there be 15 rows in the other table after adding the second entry?

YES, it will keep adding no deletions .. now the table will have 15 records. 10 AGAINST D & 5 AGAINST C.
So, the serial may be repeated again .. may be same serial 11..12..13..14..15 ...(doesn't matter).

 

[gmmastros]

In that case, use my original query, but with a slight modification.

CREATE TRIGGER dbo.tgr_DocumentRange
  On dbo.ioMaster
  For Insert
As
Begin
  Set NoCount On

  Insert
  Into   ioTransactions(ioId, ioSerialNo, BatchId)
  Select I.ioId, Numbers.Num, I.BatchId
  From   Inserted I
         Inner Join Numbers
           On Numbers.Num Between ioDocumentStartSerialNo And ioDocumentEndSerialNo
End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Dear George... Please accept my heartiest thanks..

Its working as i wanted it to ... can't thank you enough for the kind support ..

Thank a Lot .. Wish U All the Success..!!!

Best Regards,

Sam

 

[gmmastros]

You're welcome

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom