Home > Blockchain >  Oracle SQL combine columns from one table
Oracle SQL combine columns from one table

Time:04-07

i am using oracle sql developer and want to build a trigger to insert values into a table column.

My table is:

column1(num) | column2(num) | column3(var)
     1              5
     6              4
     7              3

I want to combine the first two columns, so in the end column3 should look like this:

column3(var)

       1_5
       6_4
       7_3

My Idea was:

create or replace TRIGGER   "Database"."TRIGGER"
BEFORE INSERT OR UPDATE ON   "Database"."TABLE"
FOR EACH ROW

BEGIN
    SELECT column1  ||  column2
    INTO :NEW.column3

    FROM TRIGGER;
    
END;

But column3 is still empty, can anybody tell me what i am doing wrong?

thanks in advance

CodePudding user response:

BEGIN
    :NEW.column3 :=column1  ||  column2;

END;

Calling a trigger name "Trigger" is a bad idea.

CodePudding user response:

Rather than using a trigger, you can preferably add a virtual column such as

ALTER TABLE t
ADD (
     col3 AS (col1||'_'||col2)
    );

which always will depend on those two columns, and any DML is not allowed, already not needed, it's good for displaying purposes with no interfering human factor.

Demo

  • Related