SQL Server 2010 - vertical table needing a trigger counter, insert

Hi Experts,

 I have a vertical table.
  tblNAsAffiliation
For the experts that do not know what a  vertical table is:
multiple table(s) into one.  like:
Company A
Company B
and so on...
they should be in separate tables
but are in one table and are defined by what is called folders.
Because this is  vertical table there is no "IDENTITY column"

Like Cntr3 has ONLY numbers for that one company/folder '3A' (yes '3A' is another company) and the numbers get reset for each folder or have 0's in the column Cntr3 on all records for another folder/company. But the Folder for this post has numbers that are located in Cntr3. where Affiliations = '3A'.  The column Affiliations is where the folders are named and are separated by there names.

I have started a trigger:
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 need to add a counter before the insert on tblNAsAffiliation.Cntr3, so before this:
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

so something like tblNAsAffiliation.Cntr3 = Max(Cntr3) + 1

I don't want to reuse a counter if the row with that counter was deleted from the primary table tblNAsAffiliation
On the external table tbl3ACertifiateID:
I have all the matching AffiliationID number and there are as many records as there are records in the primary table where Affiliations = '3A'

So basically I just need the proper syntax for adding the counter to the trigger.

I hope this is understandable for the experts, it should be a simply trigger.

Also I would like to mention that I am new to doing triggers so please don't let any confusion stop you from helping.

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.

chaauCommented:
If you have the SQL Server 2012 or later you can use the SEQUENCE. However, you will have to create the sequences for each company
0
Amour22015Author Commented:
Hi,

I have SQL Server 2010 on the DB's so that will not help.

That is what I am looking for is to create a sequence but based on:
Where tblNAsAffiliation.Affiliation = '3A'  --only one company

So something like:
the insert table: tblNAsAffiliation
Set tblNAsAffiliation.Cntr3 = max(Cntr3) + 1
Where tblNAsAffiliation.Affiliation = '3A'

I am looking for the correct syntax to add this to the existing trigger that I have already created.


Please help and thanks
0
Amour22015Author Commented:
This is what I am trying to fix note that there is 2 steps to this trigger.  One to get the counter and another to insert into an external table to keep track of newly added counters/records:
CREATE TRIGGER [dbo].[trgInsAffiliation]
ON [dbo].[tblNAsAffiliations]
AFTER 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


But I am getting syntax errors and I know this is almost there to what I am looking for:
Msg 286, Level 16, State 1, Procedure trgInsAffiliation, Line 7
The logical tables INSERTED and DELETED cannot be updated.

Please help and thanks
0
chaauCommented:
It is not hard to fix your code. The fix is to use BEFORE INSERT:
CREATE TRIGGER [dbo].[trgInsAffiliation]
ON [dbo].[tblNAsAffiliations]
BEFORE 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

However, you should think about this. What if the last record that you have inserted is deleted from tblAffiliation. In this case the max(Cntr3) will be the previous record. You will then have a problem in your tbl3ACertificateID table.
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
Amour22015Author Commented:
Great Job Thanks
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
Query Syntax

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.