Jump to content

Trigger - Update History Duplicates Every Record from Source File To History File

Recommended Posts

Hi, this is the first time I've tried triggers. I'm trying to keep a record of New Records Entered, Updates and Deletions by storing these in a history named Survey_Collection_HISTORY file. The problem is when I do, say, an update, every record from my source database (Survey_Collection_Table) gets entered into the history file. Here's the trigger structure:-


I have 330 records in the source file. These all get entered into the history file every time I make an update to the source file. So making a second update it increments the history file with another 330 records.

I've followed the videos and the Caspio description links but just can't see where I've gone wrong. Any help would be appreciated.

Cheers, Ron

Link to comment
Share on other sites

Your issue is with the 'SELECT FROM' block. 

Instead of selecting Survey_Collection_Table_, select #inserted from the dropdown list.

#inserted represents the record that was just interacted with.

The way that trigger is built now will indeed select all records in the Survey_Collection_Table and insert them into the History table. Using #inserted will only use the record that was updated, inserted, or deleted.

Link to comment
Share on other sites

Not a problem! Triggers are super helpful. I probably have around 100 of them actively running every day.

One thing I would recommend is to split your triggers out into 1 interaction each. 1 Insert Trigger, 1 Update Trigger, 1 Delete Trigger. 

You are only allowed to use one trigger type per table. Often times, if you start using triggers more, making a single trigger that runs off of multiple interaction types (insert, update, delete) will often lead to issues, such as if you wanted to send an email on Insert and not on Update (one of many reasons). If this happens in the future, you'd then need to take the extra time to break out the triggers individually with the correct blocks for each, and it can get pretty tough.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...