触发进行销售以更新发送表 [英] trigger for when a sale is made to update the despatch table

问题描述

我想知道在新销售插入销售表后，如何创建触发器以更新发货表。我猜，代码的前几行将看起来像这样：

 创建触发器sale_trig 
插入后sale_id on sales 
 ..... 
  

但我不知道如何做休息。
我使用iSQL Plus。

销售表包括： sale_id ， sale_price ， sale_date ， no_of_prods ， prod_id ， cust_id ， desp_id 。
发送表包含： desp_id ， shelfLoc ，数量， prod_id ， prod_name 。

我猜，因为我没有在发送表中的 sale_id ，我需要先添加它，并使它成为外键，但我不确定

我正在为数据库创建测试数据。他们给我们的模式说，我们需要在发送表中包含 prod_name ，但我认为我们只需要将它链接到 prod_id 。

确定也许不会更新。它只是一个新的插入到发送表然后？我使用这个为iSQL Plus。对于 shelfLoc ，我们被要求通过使用随机生成器生成随机字符串。那么我如何将它链接到触发器？ （我希望有意义）

解决方案

我不知道为什么你的DESPATCH表有PROD NAME列 - 可以通过PROD ID上的PRODUCT上的外键查找来导出。它绝对需要一个链接到SALES，否则如何将调度程序能够找到地址发送它。我还认为它需要一个DATE_DISPATCHED列，这将只有在订单已邮寄后填充。

你不说你正在使用的数据库。我将使用Oracle，因为它是我最熟悉的一个。

 创建或替换触发器sale_trig 
插入每行后的销售
 begin 
 insert into despatch 
（desp_id 
，shelfLoc 
，quantity 
，prod_id 
，prod_name 
，sales_id）
值
（despatch_sequence .nextval 
，dbms_random.string（'U'，5）
，：new.no_of_prods 
，：new.prod_id 
，：new.prod_name 
， new.sales_id）; 
 end; 
 / 
  

注意，我不得不对你的数据模型做一些假设，未提供足够的资讯：

1. desp_id已填入序列


2. 
   种查找


3. sales.no_of_prods映射到
   despatch.quantity

修改：在重新标记您的问题时，更新标记让我重新阅读了您的问题。你说你想更新DESPATCH表，但我不明白为什么。为什么在下订单之前有DESPATCH记录？那么有什么更新？

编辑2：我已修改触发器代码以反映您的回应。

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, but I'm not sure.

EDIT

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)

解决方案

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.

这篇关于触发进行销售以更新发送表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！