SQL Server 2010 - Trigger, Sequntial number needed

Hi Experts,

 I have a vertical table.
  tblNAsAffiliation

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

 I would like to have a sequential # based on Cntr3
 I have made a separate table to hold the current numbers:
tbl3ACertificateID
 AffiliationID (PK, int, not null) -- this is for linking to the tblAffiliation
 CertID (int, null)

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

  this table has multiple folders and the numbering is separate.

  So that is why I created a separate table to keep track of the sequential number based on 3A.


 Here is some data:
 AffiliationID      Affiliation      Cntr3
7887                    3A      1
8612                    3A      2
7884                    3A      4
7885                    3A      12
7886                    3A      17
7888                    3A      20
7889                    3A      25
7890                    3A      26
7891                    3A      29
7892                    3A      31
7999                  3A    1850

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
 I don't want to reuse a counter if the row with that counter was deleted


Where tblNAsAffiliation.Affiliation = '3A' insert the new value
  from tblNAsAffiliation.Cntr3 into tbl3ACertificateID.CertID And tblNAsAffiliation.AffiliationID into tbl3ACertifiateID.AffiliationID

And the end I want:
The new record in:
tblNAsAffiliation
tbl3ACetifiateID
so there are 2 tables involved.
trigger to be based on tblNAsAffiliation

  So I would need help with 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 have to add the sequential # + 1

Please help and thanks
Amour22015Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Amour22015Author Commented:
Wow where are the experts.  This has now been posted since 9:30 am and it is now 1:42 pm.  Wow Experts.com is great...
0
Brian CroweDatabase AdministratorCommented:
I have read through both of your questions multiple times and I simply don't understand what you are asking for.  It's like someone took a question and translated it into 3 languages before finally translating it back into English.

What is a "vertical table"?  Is there such a thing as a "horizontal table" where you add columns instead of rows?

" I would like to have a sequential # based on Cntr3" ... do you mean an IDENTITY column?

The "experts" that answer questions in here have jobs of their own and don't want to spend 30 minutes trying to decipher a question before even attempting an answer.  I would expect that at least a dozen of us have read your questions and decided it wasn't worth our time to swap 20 responses to try and understand your need.

At a minimum please provide table schema preferably in the form of CREATE TABLE scripts and sample data in the form of INSERT statements to populate those tables.  If you put the time in to asking the question you are more likely to get a precise and accurate response.
0
Amour22015Author Commented:
Q.) What is a "vertical table"?  Is there such a thing as a "horizontal table" where you add columns instead of rows?
A.) Yes even PWC is rewriting there DB's because of there vertical table(s)

A vertical table is:
multiple table(s) into one.  like:
Company A
Company B
and so on...
they should be in sep tables
but are in one table and are defined by what is called folders.


Q.) do you mean an IDENTITY column
A.) NO,  because of this being a vertical table there can not be a "IDENTITY column"
Like Cntr3 has ONLY numbers for that one company/folder and the numbers get reset for each folder or have 0's in all records for that folder.

Q.) The "experts" that answer questions in here have jobs of their own and don't want to spend 30 minutes trying to decipher a question before even attempting an answer.  I would expect that at least a dozen of us have read your questions and decided it wasn't worth our time to swap 20 responses to try and understand your need.
A.) then why am I paying x amount of dollars to be a member of experts.  Also this question is very clear and straight to the point.  I am only asking for help with the current trigger on adding a counter for max(Cntr3)+1  before the insert takes place, this question should be easy.

This is a very simple question I even provided a starting point.  Just to add a counter on Cntr3,  that should get a simply answer.   I provide the information needed for this simply question.  Just some are picky on how it is done.

Thanks for helping.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Shaun KlineLead Software EngineerCommented:
Is this something as simple as declaring a variable and selecting into the variable the max value of Cntr3?
Declare @MaxCntr int

SELECT @MaxCntr = ISNULL(MAX(Cntr), 0) + 1 FROM <your table>

Open in new window


Then using that value in your SQL statement?
0
Amour22015Author Commented:
But I need the whole trigger so that there is no syntax errors.

So something like:
CREATE TRIGGER [dbo].[trgInsAffiliation]
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
  IF EXISTS(SELECT 1 FOR inserted WHERE Affiliation = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT MAX(Cntr3)+1
                 FROM tblAffiliation
                 WHERE Affiliations = '3A')

    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 this trigger has errors:
Incorrect syntax near Where expecting ID.

Please help and thanks
0
Amour22015Author Commented:
I corrected that syntax error

But I now have another error:
Msg 286, Level 16, State 1, Procedure trgInsAffiliation, Line 7
The logical tables INSERTED and DELETED cannot be updated.

I know this trigger is almost there on what I am looking for it is just that the syntax is not correct.

Please help and thanks
0
PortletPaulfreelancerCommented:
Any money that is paid is paid to a company that keeps the servers running. The vast majority* of experts are not hired or employed by that company - we volunteer our time. That can mean that complex and/or unclear questions are skipped. Whilst I understand this would be frustrating for you, but spending a lot of voluntary time on a single question can also be frustrating for us.
---
* some employees of E-E can contribute as experts, but they do that voluntarily I  believe.


Further, whilst you may believe the question is well laid out and explained, I also struggle to understand it, and believe many others would struggle with it.

There is a very simple and very effective method for writing out questions, it involves:
a. provide sample data (and by inference the relevant schema)
b. an expected result based on the sample data
You have elements of these in your question to this but there isn't a clear identification of the expected result in my opinion.

There is also a problem (in my mind) with the word sequential. For example in the supplied data (for a single "folder") it has 1, 2,4, 12 .... ,1850

7887                    3A      1
8612                    3A      2
7884                    3A      4
7885                    3A      12
...
7999                  3A    1850

Why are there gaps? (or are you trying to re-calculate this entire column?)

What is the next expected value? 1851?

Why is Cntr3 = 2 associated with the highest AffiliationID value?

Is there datetime_created or datetime_updated columns on this table? (if not, why not?)

=================

does this query help at all?

select
   AffiliationID
 , Affiliation
, ROW_NUMBER() OVER(PARTITION BY [Affiliation] ORDER BY (select 1)) as rn
from your_taable

the function ROW_NUMBER() produces a unique integer value, starting at 1 incremented by 1, for each "partition" which in your case would be the column Affiliation.

==============
0
Amour22015Author Commented:
Actually I am looking for something some thing like:
CREATE TRIGGER [dbo].[trgInsAffiliation]
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
  IF EXISTS(SELECT 1 FROM inserted WHERE Affiliation = '3A')
        UPDATE inserted
        SET Cntr3 = (SELECT MAX(Cntr3)+1
                 FROM tblAffiliation
                 WHERE Affiliations = '3A')

    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

Note that this trigger is doing 2 things:
1. adding the counter
2. Inserting the new value from the primary table to the external table to keep track of newly added records.


But I get errors:
Msg 286, Level 16, State 1, Procedure trgInsAffiliation, Line 7
The logical tables INSERTED and DELETED cannot be updated.

Please help and thanks
0
Shaun KlineLead Software EngineerCommented:
Try this:

CREATE TRIGGER [dbo].[trgInsAffiliation]
ON [dbo].[tblNAsAffiliations]
FOR INSERT
AS
Begin
  IF EXISTS(SELECT 1 FOR inserted WHERE Affiliation = '3A')
    UPDATE tblNAsAffiliations
    SET Cntr3 = (SELECT ISNULL(MAX(Cntr3), 0) + 1 FROM tblNAsAffiliations WHERE Affiliation = I.Affiliation)
    FROM Inserted
    WHERE Inserted.AffiliationID = tblNAsAffiliations.AffiliationID
	AND Inserted.Affiliation = tblNAsAffiliations.Affiliation

    Insert into tbl3ACertificateID (AffiliationID, CertID) 
    Select Distinct i.AffiliationID, @MaxCntr 
    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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I would like to help, but I am unsure of the table design.
could you please "draw" the exact (relevant) structure of the 2 tables, some current data samples of the 2, and finally in which table you want to insert (in regards to the end user) and what the result should be.
I am sure I can then build the relevant trigger code.
0
Amour22015Author Commented:
Great
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.