Link to home
Start Free TrialLog in
Avatar of Amour22015
Amour22015

asked on

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...
Avatar of chaau
chaau
Flag of Australia image

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
Avatar of Amour22015
Amour22015

ASKER

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
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
ASKER CERTIFIED SOLUTION
Avatar of chaau
chaau
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Great Job Thanks