How do you reset the SQL On Create trigger in a Logic App after a TRUNCATE TABLE?

Question

How do you reset the SQL On Create trigger in a Logic App after a TRUNCATE TABLE?

I have a basic logic app with a "When an item is created" trigger against a SQL table.

Inserting test records and it works fine.

Then ran a TRUNCATE TABLE on the test table.

After that, the logic app SQL trigger doesn't work until the new record has a value in the IDENTITY higher than the last successful run of the SQL trigger.

How can you "reset the system" so the logic app trigger will see all records as new after a TRUNCATE TABLE?

And for my understanding, how does the logic app trigger actually work "under the covers"? ... I thought it might be a trigger on the SQL table, but checked the db and can't see any trigger

The question is very confusing as written. You'll have to make it crystal clear that you aren't referring to database triggers. TRUNCATE TABLE doesn't affect how database triggers work — Panagiotis Kanavos
From your description I suspect the "trigger" isn't actually a trigger but tries to detect changes periodically by storing the last seen IDENTITY value and querying for rows with a higher ID. — Panagiotis Kanavos

Panagiotis Kanavos Panagiotis Kanavos · Accepted Answer · 2019-05-08T14:55:57

That trigger is actually a polling job that checks for items with an ID larger than the MAX(ID) encountered in the last run. Losing records is an inherent problem with polling using an IDENTITY value. In fact, you'll find someone asking why this polling technique was used in the blog post that introduced the SQL connector

Reseeding IDENTITY

The only solution when IDENTITY is used is to reseed the IDENTITY value with :

DBCC CHECKIDENT ('Person.AddressType', RESEED, 10000);

Where 10000 is a number larger than the maximum IDENTITY value used before truncation.

A more robust solution would be to change the polling implementation to detect and use SQL Server's Change Tracking. Detecting For whatever reason, that wasn't implemented.

Use Sequences instead of IDENTITY

Another solution is to use a SEQUENCE instead of IDENTITY to generate new IDs. Sequences are independent database objects so truncating a table won't affect the sequence. In fact, you can use the same SEQUENCE as the default for multiple tables

For example, you can define this sequence :

CREATE SEQUENCE MySequence
    START WITH 1  
    INCREMENT BY 1 ;

And use it as the default constraint for a primary key :

CREATE TABLE MyTable 
(
   ID bigint PRIMARY KEY 
)

ALTER TABLE MyTable  
ADD   
    DEFAULT NEXT VALUE FOR MySequence 
    FOR ID;

How do you reset the SQL On Create trigger in a Logic App after a TRUNCATE TABLE?

2 Answers