CODEWITHSUNDEEP
informix
P.S.Mahalakshmi
P.S.Mahalakshmi

Reputation: 19

Create a Trigger to genearate a random alphanumeric string in informix

To create a trigger before insert using Informix database. When we try to insert a record into the table it should insert random alphanumeric string into one of the field. Are there any built in functions?

The table consists of the following fields:

and I am running

insert into employee(age) values(10);

The expected output should be something as below:

 id age empcode
 1, 10, asf123*

Any help is appreciated.

Upvotes: 1

Views: 424

Answers (1)

Simon Riddle
Simon Riddle

Reputation: 1116

As already commented there is no existing function to create a random string however it is possible to generate random numbers and then convert these to characters. To create the random numbers you can either create a UDR wrapper to a C function such as random() or register the excompat datablade and use the dbms_random_random() function. Here is an example of a user-defined function that uses the dbs_random_random() function to generate a string of ASCII alphanumeric characters:

create function random_string()
returning varchar(10)
define s varchar(10);
define i, n int;
let s = "";

for i = 1 to 10
  let n = mod(abs(dbms_random_random()), 62);
  if (n < 10)
  then
    let n = n + 48;
  elif (n < 36)
  then
    let n = n + 55;
  else
    let n = n + 61;
  end if
  let s = s || chr(n);
end for

return s;
end function;

This function can then be called from an insert trigger to populate the empcode column of your table.

Upvotes: 1

Related Questions