Jump to content
  • 0

Triggers: Adding just one distinct value when rows are added


niteowl2

Question

I have a customer order table that list each item a customer purchases along with their name, order date.  These are automatically populated when someone places an order, therefore if John Doe orders 5 items, there are 5 new rows added to the table (one for each item he ordered) and his name next to each one.

I have a separate customer billing table which has order date, customer name, and boolean fields for delivered, billed, paid (we deliver directly to the customer).

I'm trying to setup a trigger that automatically adds a new row for "John Doe" in the Customer Billing table when he places an order, but when I setup the trigger I get 5 new entries of his name (b/c his name was added on each of the items in the order table). 

Anyway to do some sort of "distinct" insert with the triggers to solve this problem?

Thank you, 

james

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Hello James,

 

Would it be possible for you to share a screenshot of the Triggered Action? My initial thought would be to use the value from the #inserted table for the new record creation, and not a value from the existing table to avoid the multiple insert. By seeing a screenshot, I may be able to better understand the overall trigger flow.

Thanks,

Jason

Link to comment
Share on other sites

  • 0

Hello James,

It is not possible for a trigger to insert a new record in customer billing table since the tables are related by many to one, meaning many records with same customer name in the order table and only record to be created of that name in the billing table. By changing the design of the table, this can be achieved. Please see below screenshots of the table design and the trigger that I could implement by making this change.

 

 

 

 

 

image.png.6acb960172f70e132b8cab2314d99083.png

image.png.32fbba89a1f2378dd152fca8ea3e485a.png

image.png.8ef472506b5868b794f0e7cbe3e0e112.png

image.png.1f1f144e2dd68cb9a3894c76000c9366.png

image.png.09637a59a0a2a56af74a6e9aab5ed7ad.png

Hope this helps.

 

 

 

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.

Guest
Answer this question...

×   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.

Loading...
×
×
  • Create New...