asked on

SQL Server - Making a counter table

Hi Experts,

I have a vertical table.
tblAffiliation

I have a column with a Certificate #:
tblAffiliation.Cntr3

I would like to have the # sequential
I have to make a separate table for numbering?

It is base on:
Where tblAffiliation.Affiliations = '3A' (Folder)

The Column:
Cntr3 (int, null)
Number starts with 1 - 9999 is current on what is in the tblAffiliation.Cntr3 Column
so something like: Max(Cntr3) + 1?

Trigger is best way to handle this?
with an external table to keep track of the sequential #?
Cntr3 column is not sequential to All Folders (3A = 1 Folder) it is only sequential to one Folder 3A .

this table has multiple folders and the numbering is separate.

So I will need a separate table to keep track of the sequential number based on 3A.

So where Affiliations = '3A' then do the sequential numbering on Cntr3 Column.

Here is some data:
Contact1       Contact2       Affiliation      Cntr3
1720        10631        3A      1
1720        10631        3A      2
4        10129        3A      4
12        10134        3A      12
17        10136        3A      17
20        10138        3A      20
25        10139        3A      25
26        10141        3A      26
29        10142        3A      29
25        10140        3A      31
26        10141        3A      32
26        10141        3A      33

I need help with the setup on this?

Please help and thanks...

Scott Pletcher

So what specifically are you trying to do?

Just assign the next row to be inserted for '3A' a value of 34? Or renumber the existing one starting at 1 (:shudder:, bad idea)?

If it's just assign the next value, you don't need a separate table. You just need an index on:
( affliliations, cntr3 desc )

I can then write you a trigger that will quickly and easily assign the next sequential cntr3 number(s) for row(s) being INSERTed.

Amour22015

ASKER

Hi,

I am looking to do this:
Max(Cntr3) + 1

Also I should mention on all other folders the Cntr3 column is 0

So the only time that there is a sequential # is based only on the Folder 3A

Q.) Just assign the next row to be inserted for '3A' a value of 34?
A.) Just assign the next row to be inserted for '3A a value of Max(Cntr3) + 1
So take the maximum number and add 1. Please no renumbering.

Q.) If it's just assign the next value, you don't need a separate table. You just need an index on:
( affliliations, cntr3 desc )
A.) No because the other Folders that are in the table do not have a numbering in Cntr3 plus in that column the other folders currently have 0

Q.) I can then write you a trigger that will quickly and easily assign the next sequential cntr3 number(s) for row(s) being INSERTed.

A.) Yes but it has to be based on Affiliation = 3A (Folder)

I would agree with the trigger a lone but what if a record was deleted? isn't that the reason for the external table (counter table)?

Thank You for helping.

Scott Pletcher

>> I would agree with the trigger a lone but what if a record was deleted? isn't that the reason for the external table (counter table)? <<

I don't know. So rows can be deleted? And you don't want to reuse a counter if the row with that counter was deleted? Yes, in that case you must use a separate table.

Amour22015

ASKER

Q.) So rows can be deleted
A.) yes

Q.) don't want to reuse a counter if the row with that counter was deleted
A.) Yes

So it looks like I need a external table with a trigger.
So columns needed for the external table:
AffiliationID (PK, int, not null) -- this is for linking to the tblAffiliation
Cntr3 (int, null)

with a trigger

Name table: tblAffiliationCntr3Cnt

Thanks

Scott Pletcher

I thought the column name was "Affiliations"?

CREATE TABLE tblAffiliationCntr3Cnt (
Affiliations int PRIMARY KEY,
Cntr3 int NULL
)
--initialize the table
INSERT INTO tblAffiliationCntr3Cnt ( Affiliations, Cntr3 )
SELECT Affiliations, MAX(Cntr3) AS Cntr3
FROM tblAffiliation
GROUP BY Affiliations
ORDER BY Affiliations

Scott Pletcher

Something like this for the trigger. I'm fairly sure it will need further testing:

CREATE TRIGGER tblAffiliation__TRG_INSERT
ON tblAffiliation
AFTER INSERT
AS
SET NOCOUNT ON;

DECLARE @updates TABLE (
Affiliations varchar(10),
Cntr3 int,
PRIMARY KEY(Affiliations, Cntr3)
)

UPDATE a
SET Cntr3 = ac.Cntr3 + i.row_num
OUTPUT Affiliations, Cntr3 INTO @updates
FROM (
SELECT Affiliations, identity_column, ROW_NUMBER() OVER(PARTITION BY Affiliations ORDER BY Contact1, Contact2) AS row_num
FROM inserted
) AS i
INNER JOIN tblAffiliation a ON
i.Affiliations = a.Affiliations AND
i.identity_column = a.identity_column
INNER JOIN tblAffiliationCntr3Cnt ac WITH (ROWLOCK,XLOCK) ON
ac.Affiliations = a.Affiliations

UPDATE tblAffiliationCntr3Cnt
SET Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliations = tblAffiliationCntr3Cnt.Affiliations)

GO --end of trigger

SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Hi Vitor Montalyao

So if this:

1,2,3,4,6,7,8,20

So this would use:
5,9.10.11.12.13.14.15.16.17.18.19.21.22........

As the next numbers?

Thanks for helping.

Vitor Montalvão

No. Sequence only stores the last value used. It won't fill any gaps so in your example it will store 20 and then the next value will be 21.

Vitor Montalvão

But you can always define the next value of a Sequence so it will fill the gap for you:

ALTER SEQUENCE MySequence RESTART WITH 5

Open in new window

Unfortunally you'll need to do it manually until you fill all the gaps.

Amour22015

ASKER

I have set up an external table to keep track of the sequential #'s.

tbl3ACertificateID

That has a matching number to the tblNAsAffiliation table.

So in the new table I have:
[AffiliationID]
,[CertID]

In table: tblNAsAffiliation I have the matching number column: AffiliationID and of course the: Cntr3

So your example would need changing?

Where tbl3ACertificateID.AffiliationID = tblNAsAffiliation .AffiliationID insert the new value
from tbl3ACertificateID.CertID into tblNAsAffiliation.Cntr3

So I would need help with this..

Amour22015

ASKER

Ok so I will need help with this trigger:

CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON tbl3ACertificateID
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    AffiliationID (PK, int, not null),
    CertID int,
    PRIMARY KEY(AffiliationID, CertID)
    )

 UPDATE a
 SET Cntr3 = ac.Cntr3 + i.row_num
 OUTPUT Affiliations, Cntr3 INTO @updates
 FROM (
     SELECT Affiliations, identity_column, ROW_NUMBER() OVER(PARTITION BY Affiliations ORDER BY Contact1, Contact2) AS row_num
     FROM inserted
 ) AS i
 INNER JOIN tblAffiliation a ON 
     i.Affiliations = a.Affiliations AND
     i.identity_column = a.identity_column
 INNER JOIN tblAffiliationCntr3Cnt ac WITH (ROWLOCK,XLOCK) ON
     ac.Affiliations = a.Affiliations

 UPDATE tblAffiliationCntr3Cnt
 SET Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliations = tblAffiliationCntr3Cnt.Affiliations)

 GO --end of trigger

Open in new window

Please help and thanks

Amour22015

ASKER

A post that should take about 1 hour,

takes about 1 hour just to get a response.
Then
takes more than 2 days and still no answer
Wow this is experts.com?

Amour22015

ASKER

Ok, I have this:

CREATE TRIGGER trig_Update_Employee
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
    Insert into tbl3ACertificateID (AffiliationID, CertID) 
    Select Distinct i.AffiliationID, i.Cntr3 
    from Inserted i
    Left Join tbl3ACertificateID e
    on i.AffiliationID = e.AffiliationID and i.Cntr3 = e.CertID
    where i.Affiliation = '3A'
End

Open in new window

But I still need to add the sequential # + 1

Please help and thanks

Scott Pletcher

I did the complete trigger, adding the numbers and all. Add a check for " Affiliation = '3A' " in it if you need it. Obviously I can't actually run the trigger to test it since I don't have those tables.

Amour22015

ASKER

Hi ScootPletcher,

I have tried your trigger but I get error and some logic is not yet correct:

CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON dbo.tblNAsAffiliations
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    Affiliations varchar(10),
    Cntr3 int,
    PRIMARY KEY(Affiliations, Cntr3)
    )

 UPDATE a
 SET Cntr3 = ac.Cntr3 + i.row_num
 OUTPUT Affiliation, Cntr3 INTO @updates
 FROM (
     SELECT Affiliation, identity_column, ROW_NUMBER() OVER(PARTITION BY Affiliations ORDER BY Contact1, Contact2) AS row_num
     FROM inserted
 ) AS i
 INNER JOIN tblNAsAffiliations a ON 
     i.Affiliation = a.Affiliation AND
     i.identity_column = a.identity_column
 INNER JOIN tbl3ACertificateID ac WITH (ROWLOCK,XLOCK) ON
     ac.AffiliationID = a.AffiliationID

 UPDATE tbl3ACertificateID
 SET i.Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliation = tblAffiliationCntr3Cnt.Affiliations)

 GO --end of trigger

Open in new window

Like I am not doing this and there is no match on both tables to this column:
SET i.Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliation = tbl3ACertificateID.Affiliation)

The only match is:
SET i.Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE AffiliationID = tbl3ACertificateID.AffiliationID)

Please help and thanks

Amour22015

ASKER

Plus I do not see anything about:
tblNAsAffiliation.Affiliation = '3A'

CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON dbo.tblNAsAffiliations
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    Affiliations varchar(10),
    Cntr3 int,
    PRIMARY KEY(Affiliations, Cntr3)
    )

 UPDATE a
 SET Cntr3 = ac.Cntr3 + i.row_num
 OUTPUT Affiliation, Cntr3 INTO @updates
 FROM (
     SELECT Affiliation, identity_column, ROW_NUMBER() OVER(PARTITION BY Affiliations ORDER BY Contact1, Contact2) AS row_num
     FROM inserted
 ) AS i
 INNER JOIN tblNAsAffiliations a ON 
     i.Affiliation = a.Affiliation AND
     i.identity_column = a.identity_column
 INNER JOIN tbl3ACertificateID ac WITH (ROWLOCK,XLOCK) ON
     ac.AffiliationID = a.AffiliationID

 UPDATE tbl3ACertificateID
 SET i.Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliation = tblAffiliationCntr3Cnt.Affiliations)

 GO --end of trigger

Open in new window

Amour22015

ASKER

Also don't I need to first put in the new record into the external table (tbl3ACertificateID)? within the trigger like I mention in: ID: 40810914

Please help and thanks

CREATE TRIGGER tblAffiliation__TRG_INSERT
 ON dbo.tblNAsAffiliations
 AFTER INSERT
 AS
 SET NOCOUNT ON;

 DECLARE @updates TABLE (
    Affiliations varchar(10),
    Cntr3 int,
    PRIMARY KEY(Affiliations, Cntr3)
    )

 UPDATE a
 SET Cntr3 = ac.Cntr3 + i.row_num
 OUTPUT Affiliation, Cntr3 INTO @updates
 FROM (
     SELECT Affiliation, identity_column, ROW_NUMBER() OVER(PARTITION BY Affiliations ORDER BY Contact1, Contact2) AS row_num
     FROM inserted
 ) AS i
 INNER JOIN tblNAsAffiliations a ON 
     i.Affiliation = a.Affiliation AND
     i.identity_column = a.identity_column
 INNER JOIN tbl3ACertificateID ac WITH (ROWLOCK,XLOCK) ON
     ac.AffiliationID = a.AffiliationID

 UPDATE tbl3ACertificateID
 SET i.Cntr3 = (SELECT MAX(Cntr3) FROM @updates WHERE Affiliation = tblAffiliationCntr3Cnt.Affiliations)

 GO --end of trigger

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Wow the naming in the trigger might be all wrong.

The primary key in the external table (tbl3ACertificateID) is AffiliationID
This is what my external table has in it:
tbl3ACertificateID
AffiliationID (PK, int, not null) -- this is for linking to the tblAffiliation
CertID (int, null)

Only 2 columns.

There is no Affiliations column.
There is a Affiliation column and that should equal '3A'
so: tblNAsAffiliation.Affiliation = '3A'

This that you have:
DECLARE @updates TABLE (
Affiliations varchar(10),
Cntr3 int,
PRIMARY KEY(Affiliations, Cntr3)
)

I don't think is correct but I don't understand the logic to change it.

Maybe if @updates = external table (tbl3ACertificateID) then:
DECLARE @updates TABLE (
AffiliationID varchar(10),
CertID int,
PRIMARY KEY(AffiliationID, CertID)
)
But I am just ? at this point.

You trigger looks good I just need help with the logic.

Please help and thanks.

Amour22015

ASKER

Q.) Does the table have an identity column?
A. ) No

I did this:
CREATE TABLE tblAffiliationCntr3Cnt (
Affiliations int PRIMARY KEY,
Cntr3 int NULL
)

But changed to:
CREATE TABLE tblAffiliationCntr3Cnt (
AffiliationID int PRIMARY KEY,
CertID int NULL
)

On this:
INSERT INTO tblAffiliationCntr3Cnt ( Affiliations, Cntr3 )
SELECT Affiliations, MAX(Cntr3) AS Cntr3
FROM tblAffiliation
GROUP BY Affiliations
ORDER BY Affiliations

I did this:
INSERT INTO tbl3ACertificateID ( AffiliationID, CertID)
SELECT AffiliationID, Cntr3
FROM tblNAsAffiliation
Where Affiliation = '3A'

So in the external table (tbleACertificateID)
I have all records where tblNAsAffiliation.Affiliation = '3A'
Should I have done this?

Thanks for helping.

Amour22015

ASKER

I just found out that this:
So in the external table (tbleACertificateID)
I have all records where tblNAsAffiliation.Affiliation = '3A'
Should I have done this?

is correct because that way we can keep track of historical data:
If client asks what happened to this AffiliationID number we now have a external table to say yes/no it was not in the table.

Please help and thanks.

Scott Pletcher

I was just putting the highest value in there to track it. All the rows will not work the way the code is written now. If you need the history, that should go in a different table.

Amour22015

ASKER

Great Thanks