Home / Questions / 212418
Accepted
DDR
Asked: 2020-11-10 19:26:47 +0800 CST 2020-11-10 19:26:47 +0800 CST

How to concatenate a variable int and str in a trigger?

  • 772

I am doing a trigger in sql which concatenates a word with a number, like this:

create or replace trigger TG_curso_Auto
before insert on cursos
for each row
declare  var int;
begin
    select count(*)+1  into var from cursos;
    :new.siglas := concat(:new.siglas,'-',var);
end;
/

Here var is an integer type variable that I need to concatenate with a string, but I don't know how to pass it to a string, the courses table simply has an id and an acronym

create table cursos
     id number(7) not null,
     siglas varchar(4) not null);

To better explain the var starts from 1 and as something is added to it, it adds 1 by 1... when trying to insert in the table courses for example ( insert into cursos values (12345,'Bases')) you should put in the column acronyms Bases-1 then something like acronyms-2 , initials-3 ...

Any ideas how to solve this problem or is it not possible to do this?

Thank you!

sql

2 Answers

  1. to concatenate you can use the double slashes:campo1 || '-' || campo2

    • 1
  2. Best Answer

    uses the function to_char, for example as follows:

    :new.siglas := :new.siglas || '-' || to_char(var);

    In this case, the field will remain nullif it already had a null value. If it is not the desired behavior, you can also use coalesce, for example

    :new.siglas := coalesce(:new.siglas, '') || '-' || to_char(var);
    • 1