CODEWITHSUNDEEP
sqloracle-databaseconcatenation
frnzsk
frnzsk

Reputation: 11

Oracle SQL combine columns from one table

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

Upvotes: 0

Views: 56

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

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

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

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

END;

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

Upvotes: 0

Related Questions