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:
But I need to add a counter before the insert on tblNAsAffiliation.Cntr3, so before this:
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...
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
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
so something like tblNAsAffiliation.Cntr3 = Max(Cntr3) + 1I 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...
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
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.Affiliat ion = '3A' --only one company
So something like:
the insert table: tblNAsAffiliation
Set tblNAsAffiliation.Cntr3 = max(Cntr3) + 1
Where tblNAsAffiliation.Affiliat ion = '3A'
I am looking for the correct syntax to add this to the existing trigger that I have already created.
Please help and thanks
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.Affiliat
So something like:
the insert table: tblNAsAffiliation
Set tblNAsAffiliation.Cntr3 = max(Cntr3) + 1
Where tblNAsAffiliation.Affiliat
I am looking for the correct syntax to add this to the existing trigger that I have already created.
Please help and thanks
ASKER
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:
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great Job Thanks