Oracle SQL combine columns from one table

Question

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

As a side observation - do NOT enclose object names in double-quotes. This makes the names case sensitive, and you will always and forever have to enclose them in double-quotes, and use the correct case, when referencing them. And even that aside, I do hope your schema name 'Database', trigger name 'TRIGGER' and table name 'TABLE' are not the names you really use . . . — EdStevens
– EdStevens, Commented Apr 6, 2022 at 20:09

Barbaros Özhan · Accepted Answer · 2022-04-07 05:52:38Z

1

Rather than using a trigger, you can preferably add a virtual column after dropping the existing one such as

SQL> ALTER TABLE t DROP COLUMN col3;

SQL> 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

edited Apr 7, 2022 at 5:52

answered Apr 6, 2022 at 19:28

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5683823 Over a year ago

He will have to drop the existing col3 first; but this is definitely the best approach.

OldProgrammer · Accepted Answer · 2022-04-06 19:09:47Z

0

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

END;

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

answered Apr 6, 2022 at 19:09

OldProgrammer

12.2k4 gold badges27 silver badges52 bronze badges

Collectives™ on Stack Overflow

Oracle SQL combine columns from one table

My Idea was:

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

My Idea was:

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related