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

[RonAnderson]

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

[kpcollier]

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.

[RonAnderson]

@kpcollier Wow. Thank you so much. It now works. I've looked back through the web pages and this isn't clear at all so once again... Thanks

Best regards

Ron

[kpcollier]

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.