How to make trigger fire again when duplicating?

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! 

​

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()

And to add to that ​@Niels Koop you really shouldn't want to pursuit a 'double’ trigger or chain, just to have it noted :)

Hi ​@Niels Koop,

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? 

​@Erwin Ekkel no we only assigned it to the insert variant. If we update an order we should not want the counter to change. 

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. 

Turns out that ​@Mark_Plaggenborg his solution did work. We were resetting the value to null, which made the trigger not fire because it doesn't work on null values. We are now resetting the value to zero (0), and it does work now. Thanks all for the help!