Please help, need an insert trigger to insert the group_ID (next sequential number) of a partitioned group. Much like a dense_rank of repeating group of names.
When a new record is created, if the name exists, then the group_id will be this name's group_id. If the name does not exist, then the group_id will be auto generated to a new number that hasn't been used.
Not sure what this is called.
Create table T1 (
Group_Name Varchar2(50)
,Group_ID Number
);
commit;
Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );
Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );
Insert into T1 (Group_Name, Group_ID) Values ('ABC', 1 );
Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );
Insert into T1 (Group_Name, Group_ID) Values ('EFG', 2 );
Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );
Insert into T1 (Group_Name, Group_ID) Values ('HIJ', 3 );
COMMIT;
SELECT * FROM T1;
GROUP_NAME GROUP_ID
---------- ---------
ABC 1
ABC 1
ABC 1
EFG 2
EFG 2
HIJ 3
HIJ 3
Need An insert trigger to insert the GROUP_ID based on this logic:
- if the inserted value for GROUP_NAME already exists in T1, then GROUP_ID gets its already defined GROUP_ID for GROUP_NAME
- if GROUP_NAME does not exist in T1, insert the next sequential number for a this new "Grouping" for GROUP_ID.
Insert examples and what GROUP_ID should be are below:
INSERT INTO T1 (GROUP_NM) VALUES('ABC');
*** Since 'ABC' is found in the table with Group_ID of 1, the insert trigger, should evaluate and update the table of GROUP_ID to 1.
INSERT INTO T1 (GROUP_NM) VALUES('PEP');
*** Since 'PEP' group name inserted is not found in the table T1, the trigger should auto generate the next value of 4 for the GROUP_ID.
Best Answer
Not Possible
Your suggested Implementation of your Business Requirement is not possible.
You cannot
select
from tableT1
from within a trigger onT1
. There are hacks around such limitation. But, that is what they are: hacks. One of them has a side effect of preventing you from performing arollback
.In all cases: you'll still want to define a
sequence
forgroup_id
.Work Around - Correct your schema design
If you want to ensure that each Unique
group_name
contains a Uniquegroup_id
, then you need to create a table that enforces such data logic.Then, and only then, you can use a trigger. (I'd still not advise it.)
Work Around - TAPI
One work is the use of Table APIs (TAPI).
This is a
Package
that performs all necessary tasks to perform aninsert
,update
, ordelete
. Instead of your application performing the DML, the application calls the TAPI to perform the DML.If necessary, you can wrap the
Package
with aVIEW
and an INSTEAD OFtrigger
.Package Spec:
Package Body: