Jump to content
  • 0
niteowl2

Triggers: Adding just one distinct value when rows are added

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

Share this post


Link to post
Share on other sites

2 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

Share this post


Link to post
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.

 

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×