I have a vertical table.
I have a column with a Certificate #:
I would like to have a sequential # based on Cntr3
I have made a separate table to hold the current numbers:
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
ion = '3A' insert the new value
from tblNAsAffiliation.Cntr3 into tbl3ACertificateID.CertID And tblNAsAffiliation.Affiliat
ionID into tbl3ACertifiateID.Affiliat
And the end I want:
The new record in:
so there are 2 tables involved.
trigger to be based on tblNAsAffiliation
So I would need help with this:
CREATE TRIGGER trig_Update_Employee
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'
But I still have to add the sequential # + 1
Please help and thanks