HELP! Creating Custom Primary Key in Trigger during INSERT
-
I want to generate a Batch ID in an Insert Trigger. This Batch ID is the Primary Key of the Table.
It is not possible to changed the value in the table INSERTED
Does anyone a solution for this challenge?
-
I don't understand what you mean.
N 56°04'39.16"
E 12°55'05.25" -
I have a table with 1 fields as PK called [BatchID]
The application creating the new record should not be bothered with any logic. So I want to fill this field in an Insert Trigger.
In the Trigger the changes are in the virtual table INSERTED. I assume that it contains the new values which are already flushed to the table.
However since the field is a PK it doesn't accept a Null value. I'm looking for a way to fill this field with a value BEFORE the data is flushed to the table.
-
In your trigger, try selecting from INSERTED into a temp table and adding the BatchID to the temp table. After updating the temp table with the BatchID, insert into your SQL table from the temp table.
If this doesn't work, then write youself a little stored procedure for the application to call instead of trying to directly insert a row.
-
Why don't you make the BatchID column and INT IDENTITY(1,1), that way any insert would autmatically increment the BatchID by 1?
-
An Identity for the BatchID doesn't work in my situation. The table is used for simulation purposes and a part of the records is deleted before each simulation run. An Identity would count in the next run from the highest number of the last run and so there would be gaps in the range.
Therefore we developped a procedure using the BatchID's in the gaps.
What I'm going to do is just to make a fake PK with an Identity and have the BatchID out of the PK. In that way I can set the BatchID in the Trigger.
Yes, I know I'm going around the problem, however this will work.
But still interested in the real solution.
HD
-
Have you tried using an INSTEAD OF trigger instead of (the default) AFTER trigger?
An INSTEAD OF trigger actually replaces the normal INSERT behavior, and it's up to you to haul the data out of the inserted table and INSERT it into the table. As long as something is passed (even if it is a zero or space), you can go about inserting your data into the target table as you see fit.
Then you can stay away from identity columns
-Eddie
Eddie Wuerch
MCM: SQL -
Could you use a default constraint to generate a new id if one is not supplied?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply