Hi, we have a table where we want to be able to duplicate an existing row because this will streamline our process further.
We have a field called po_number, which is being filled with a trigger. We add a counter to it which makes the record unique. However, when we duplicate an order with the same details, we want a new counter to be assigned to it, but it grabs the old counter and we can't save the record.
When we duplicate, can we set that value to 0 so that the trigger applies a new counter?
Hi Niels,
While it is not entirely clear on what you are trying to achieve, I think I might know where you want to go. Probably the solution lies within the trigger itself, since that is also the functionality that generates the number.
It might work when you are deciding the new value from the copied record, that a query also determines if there are other rows with the same details. If you want to reset your identity/counter within the table, that is not recommended.
If this process is repeating for most situations, you might want to work with revisions and generate this within a task rather than the trigger.
Hope this helps and don't hesitate to clarify a bit further if anything is unclear!
Hi, we have a table where we want to be able to duplicate an existing row because this will streamline our process further.
We have a field called po_number, which is being filled with a trigger. We add a counter to it which makes the record unique. However, when we duplicate an order with the same details, we want a new counter to be assigned to it, but it grabs the old counter and we can't save the record.
When we duplicate, can we set that value to 0 so that the trigger applies a new counter?
Without knowing your scenario and existing functionality it's a bit hard to steer into the right direction. However if it's something that ‘always’ happens under certain conditions. you could cross join 2 values to the ‘inserted’ table to have the original and the copy like cross join (values(‘original’),('copy’)) oc (type) then it shouldn't be to hard to let the counter include the copy with for example a row_number() over() or dense_rank() over()
You can disable the “Copy” action in the GUI take over the current value of “po_order”. Navigate from the menu: “Subject” → Specific table/view → “Columns”
Disable “Include in copy”:
And to add to that
Hi
Did these responses help, or do you require further assistance?
HI, thanks all for the replies. I've marked the best answer.
Hi, turns out that the best answer actually did not work and we had a false positive, so let me further clarify what it is we are trying to solve.
We have a trigger that generates a counter based on a table we have created in which we store the counters. These counters serve as unique identifiers for different things we follow (contracts, maintenance orders, etc.)
Here is the trigger code to insert the counter in a table:
DECLARE @counter_value int, @company_id int, @@table_name]_id int ;
SET @company_id = isnull(dbo.get_default_company_id(),0);
SELECT @@table_name]_id = table_name]_id FROM inserted;
--Get new counter id
EXEC dbo.get_new_counter @company_id = @company_id , @counter_subject_name = ''table_name]' , @counter_value = @counter_value output;
UPDATE table_name]
SET Counter_field] = @counter_value --@counter_value
FROM INSERTED i
INNER JOIN table_name] a ON a..table_name]_id = i..table_name]_id
Now sometimes, we have a maintenance order that uses the same repair type, same garage, etc. but with a different vehicle. We want to be able to copy that order, and only have to change the vehicle.
The problem we are currently experiencing is that the counter from the previous order (the one we clicked copy on) is also used for the newly created copy. If we disable the “include in copy” button, the counter does not get transferred over, but we are unable to create an order because the trigger doesn't fire.
How can we fix this?
I don’t see why the counter wouldn’t work with the trigger since you don’t actually use the counter_field in the code besides for the update. Did you make both an insert and an update trigger?
is this the entire trigger code? It seems like the problem is either in a different part of the trigger code, or in the dbo.get_new_counter it self.
This is dbo.get_new_counter
DECLARE @counter_id id
SELECT @counter_value = u.counter_value, @counter_id = c.counter_id
FROM counter c
LEFT JOIN counter_unused u ON u.counter_id = c.counter_id
WHERE subject_name = @counter_subject_name
AND c.company_id = @company_id
ORDER BY u.counter_value
IF (@counter_value IS NOT NULL)
BEGIN
DELETE FROM counter_unused WHERE counter_value = @counter_value AND counter_id = @counter_id
END
ELSE
BEGIN
SELECT @counter_value = c.counter_value FROM counter c WHERE c.counter_id = @counter_id
UPDATE c SET counter_value = ISNULL(counter_value, 0) + 1, counter = ISNULL(counter, 0) + 1, last_retrieval = GETDATE() FROM counter c WHERE c.counter_id = @counter_id
END
I really don’t see what could be causing this. Maybe you can share your model and i can have a quick look if i can find the cause.
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.