trigger for when a sale is made to update the despatch table

Question

I was wondering how I would go about creating a trigger to update the despatch table once a new sale has been inserted into the sales table. I am guessing that the first few lines of the code will look like this: create trigger sale_trig after insert of sale_id on sales .....

But I am not sure how to do the rest. I am using iSQL Plus.

The sales table consists of: sale_id, sale_price, sale_date, no_of_prods, prod_id, cust_id, desp_id. The despatch table consists of: desp_id, shelfLoc, quantity, prod_id, prod_name.

I'm guessing since I don't have sale_id in the despatch table, I would need to add that in first and make it a foreign key. Please help. Thank you!

Answer 1

I don't know why your DESPATCH table has the PROD NAME column - that ought to be derivable by a foreign key lookup on PRODUCT via PROD ID. It definitely does need a link to SALES, otherwise how will the dispatcher be able to find the address to send it too. I also think it needs a DATE_DISPATCHED column, which will only be populated after the order has been mailed.

You don't say database you're using. I'll use Oracle because it's the one I'm most familiar with.

create or replace trigger sale_trig 
    after insert on sales for each row
begin
    insert into despatch
      (desp_id
       , shelfLoc
       , quantity
       , prod_id
       , prod_name
       , sales_id)
    values
      (despatch_sequence.nextval
       , dbms_random.string('U', 5)
       , :new.no_of_prods
       , :new.prod_id
       , :new.prod_name
       , :new.sales_id);
end;
/

Note that I have had to make some assumptions about your data model because you haven't provided sufficient information:

1. desp_id is populated with a sequence
2. shelfloc is populated through some kind of lookup
3. sales.no_of_prods maps to despatch.quantity

Edit: whilst retagging your question the Update tag made me re-read your question. You say you want to update the DESPATCH table, but I don't understand why. Why would you have a DESPATCH record before an order has been placed? So what is there to update?

Edit 2: I have revised the trigger code to reflect your response.

Answer 2

I am creating test data for a database. The schema they have given us says that we need to include prod_name in the despatch table for some reason, but I thought we'd only need to link it with prod_id.

Ok maybe it isn't update. Would it just be a new insert into the despatch table then? I am using this for iSQL Plus. For shelfLoc we were told to generate random strings, by using the random generator. So how would I link that into the trigger? (I hope that made sense) Thanks a lot.