Oracle触发器更新列在同一个表中

I have table called M_StockCode_JB . Its fields are Code, Description, and Display.

我有一个名为M_StockCode_JB的表。它的字段是代码、描述和显示。

Display is still empty, but Code and Description has values as the following :

显示仍然为空，但是代码和描述具有如下值:

  M_StockCode_JB
-----------------------
Code    |   Description
1       |   Apple
2       |   Banana
3       |   Pinneaple

I want to add column called 'Display' which contain both data from Code and Description, I plan to use this SQL SELECT "NAME" ||'-' || DESCRIPTION FROM M_STOCKCODE_JB so it will generated like this :

我想添加一个名为“Display”的列，它包含来自代码和描述的两个数据，我打算使用这个SQL SELECT“NAME”||'-' ||描述，从M_STOCKCODE_JB，它将生成如下:

                M_StockCode_JB
    ------------------------------------------
    Code    |   Description    |   Display
    1       |   Apple          |   1-Apple
    2       |   Banana         |   2-Banana
    3       |   Pineapple      |   3-Pineapple

How can I insert to Display column as I add column Code and Description ?

如何在添加列代码和描述时插入显示列?

1 个解决方案

#1

If your Display column allways contain only "NAME" ||'-' || DESCRIPTION, and you are using Oracle 11G or higher, you may create virtual column

如果您的显示列总是只包含“名称”||'-' ||描述，并且您正在使用Oracle 11G或更高版本，您可以创建虚拟列

ALTER TABLE M_StockCode_JB ADD (Display AS (NAME ||'-' || DESCRIPTION))

In any version you may create view

在任何版本中都可以创建视图

CREATE OR REPLACE VIEW V_M_StockCode_JB AS
SELECT "NAME" ||'-' || DESCRIPTION FROM M_STOCKCODE_JB

If column need to be updatable, then you need real column with trigger

如果列需要可更新，则需要具有触发器的实列

ALTER TABLE M_StockCode_JB ADD (Display VARCHAR2(4000));
CREATE OR REPLACE TRIGGER M_StockCode_JB_trig
  BEFORE
    INSERT ON M_StockCode_JB 
    FOR EACH ROW    
BEGIN
  :new.Display = :new.NAME ||'-' || :new.DESCRIPTION;
END;
/

#1