user3718809 user3718809 - 4 months ago 7
SQL Question

Can I use a PL/SQL trigger to check category and concat to a incremental number based on category?

I got a

productID
- P0001KTC and P0001DR.

If product category is kitchen, I will assign a
productID - P0001KTC
, else if the category is dining room, then the
productID
should be
P0001DR
.

Is it possible to write a sequence inside a trigger to check the product category and assign an id as mentioned above?

sample table

APC APC
Answer

P0001KTC is the sort of smart key users love and developers hate. But the customer is king, so here we are.

Note

The OP's requirement is unclear. I have interpreted this line ...

I got a productID - P0001KTC and P0001DR

... to mean the OP wants to increment the numeric element within the product category. However, there's a distinct possibility it's just a typo, in which case the following is just a waste of my time and energy. So it goes.

Anyhoo.

The best way to implement this is with a code control table, that is a table to manage the assigned numbers. Such an approach entails pessimistic locking, which serializes access to a Product Category (e.g. KTC). Presumably your users won't be creating new Products very often, so the scaling implicates aren't severe.

Working PoC

Here's our reference table:

create table product_categories (
    product_category_code varchar2(3) not null
    , category_description varchar2(30) not null
    , constraint product_categories_pk primary key (product_category_code)
)
/

create table product_ids (
    product_category_code varchar2(3) not null
    , last_number number(38) default 0 not null
    , constraint product_ids_pk primary key (product_category_code)
    , constraint product_ids_categories_fk foreign key (product_category_code)
                   references product_categories (product_category_code)
) organization index
/

May these two tables could be one table, but this implementation offers greater flexibility. Let's create our Product Categories:

insert all
    into product_categories (product_category_code, category_description)
        values (cd, descr)
    into product_ids (product_category_code)
        values (cd)
select * from
    ( select 'KTC' as cd, 'Kitchen' as descr from dual union all
      select 'LR' as cd, 'Living Room' as descr from dual union all
      select 'DR' as cd, 'Dining Room' as descr from dual )
/

Here's the target table:

create table products (
    product_id varchar2(10) not null
    , product_category_code varchar2(3) not null
    , product_description varchar2(30) not null
    , constraint products_pk primary key (product_id)
    , constraint products_fk foreign key (product_category_code)
                   references product_categories (product_category_code)
)
/

This function is where the magic happens. The function formats the new Product ID. It does this by taking out a pre-emptive lock on the row for the assigned Category. These locks are retained for the length of the transaction i.e. until the locking session commits or rolls back. So if there are two users creating Kitchen Products one will be left hanging on the other: this is why we generally try to avoid serializing table access in multi-user environments.

create or replace function get_product_id
    ( p_category_code in product_categories.product_category_code%type)
    return products.product_id%type
is
    cursor lcur (p_code varchar2)is
        select last_number + 1
        from product_ids
        where product_category_code = p_code
        for update of last_number;
    next_number product_ids.last_number%type;
    return_value products.product_id%type;
begin
    open lcur( p_category_code);
    fetch lcur into next_number;

    if next_number > 999 then
        raise_application_error (-20000
                , 'No more numbers available for ' || p_category_code);
    else
        return_value := 'PROD' || lpad(next_number, 3, '0') || p_category_code;
    end if;

    update product_ids t
    set t.last_number = next_number
    where current of lcur;

    close lcur;

    return return_value;
end get_product_id;
/

And here's the trigger:

create or replace trigger products_ins_trg
    before insert on products
    for each row
begin
    :new.product_id := get_product_id (:new.product_category_code);
end;
/

Obviously, we could put the function code in the trigger body but it's good practice to keep business logic out of triggers.

Lastly, here's some test data...

insert into products ( product_category_code, product_description)
values ('KTC', 'Refrigerator')
/
insert into products ( product_category_code, product_description)
values ('DR', 'Dining table')
/
insert into products ( product_category_code, product_description)
values ('KTC', 'Microwave oven')
/
insert into products ( product_category_code, product_description)
values ('DR', 'Dining chair')
/
insert into products ( product_category_code, product_description)
values ('DR', 'Hostess trolley')
/
insert into products ( product_category_code, product_description)
values ('LR', 'Sofa')
/

And, lo!

SQL> select * from products
  2  /

PRODUCT_ID PRO PRODUCT_DESCRIPTION
---------- --- ------------------------------
PROD001KTC KTC Refrigerator
PROD001DR  DR  Dining table
PROD002KTC KTC Microwave oven
PROD002DR  DR  Dining chair
PROD003DR  DR  Hostess trolley
PROD001LR  LR  Sofa

6 rows selected.

SQL>