Expanding variables in a trigger in Oracle PL/SQL (IF :new.variable_name IS NULL THEN

Question

I would like to do the following in Oracle (PL/SQL) when defining a trigger:

    DECLARE
        pk_column_name VARCHAR(50) := 'id';
    BEGIN
    EXECUTE IMMEDIATE 'CREATE OR REPLACE TRIGGER table_a_trigger
        BEFORE INSERT ON table_a
        FOR EACH ROW
        BEGIN
            IF :new.pk_column_name IS NULL THEN       /* here */
                SELECT table_a_sequence.nextval
                INTO :new.pk_column_name              /* and here */
                FROM DUAL;
            END IF;
        END;';
    END;

I would like the variable pk_column_name (which contains the string 'id') to be "expanded" so I could write it like in above example:

    IF :new.pk_column_name  ....

instead of...

    IF :new.id  ....

I tried a lot of things like:

    :new."pk_column_name"
    :new.&pk_column_name
    :new.:pk_column_name

Could someone please help me?

Thanks, and best regards,

Udo

Why? You have to know the actual column name when you create the trigger in order to set the variable, so why not just use the actual name everywhere? You can't use a variable like that anyway... — Alex Poole
– Alex Poole, Commented Apr 21, 2017 at 18:15
No I don't know the actual column name, because this SQL is part of a code in a software, where the column name is not known before. — Udo
– Udo, Commented Apr 21, 2017 at 18:22
You can't create a trigger on a table if you don't know its structure at compile time. (Unless you don't refer to any of its columns, I suppose). — Alex Poole
– Alex Poole, Commented Apr 21, 2017 at 18:25
Why select from dual though? PL/SQL has this handy := assignment operator. Also in 12.1 you can set this as a column default. — William Robertson
– William Robertson, Commented Apr 22, 2017 at 22:48

Udo · Accepted Answer · 2017-04-21 18:25:34Z

0

I got it!

    ... IF :new.' || pk_column_name || ' IS NULL THEN ...

and

    ... INTO :new.' || pk_column_name || '
    FROM DUAL;

answered Apr 21, 2017 at 18:25

Udo

12 bronze badges

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

7 Comments

Alex Poole Over a year ago

Right, so you do know the column name at compile time. You didn't say you were creating it dynamically (which is an unusual thing to do).

Udo Over a year ago

Actually not. Not at compile time. The piece of software is used to dynamically rename a table and recreate triggers at runtime.

Alex Poole Over a year ago

At the time the trigger is compiled (dynamically) you do know the PK column name, otherwise you couldn't concatenate it in like that.

Udo Over a year ago

Thanks for nothing. Sorry, but I think my example was quite clear to understand. I wanted to know how to expand the variable and use it in a trigger. You said it was not possible, but I found the solution myself.

Alex Poole Over a year ago

Well, I was trying to help, but I'll free not to bother next time. Where in your question does it say that you're creating it dynamically? You showed the variable as local to the trigger, which it can't be, and isn't in your solution. I can only comment - and try to help - based on what you say in your question.

|

Collectives™ on Stack Overflow

Expanding variables in a trigger in Oracle PL/SQL (IF :new.variable_name IS NULL THEN

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related