This section discusses how to handle:
To do a uniqueness check for a key, use a select statement that is invoked by the WHEN-VALIDATE-ITEM event.
Note that a uniqueness check done in WHEN-VALIDATE-ITEM does not catch duplicates residing in uncommitted rows (for instance, a user enters uncommitted, duplicate rows in a detail block). The database constraints will catch this situation, as well as the situation where an identical key is committed by someone else between the time that the WHEN-VALIDATE-ITEM fired and your record is committed. For this reason, you do not need to write a uniqueness check in PRE-UPDATE or PRE-INSERT.
If there is a single unique key field, always call the CHECK_UNIQUE package from WHEN-VALIDATE-ITEM for that field.
If the unique combination is comprised of multiple fields, call the CHECK_UNIQUE package from the WHEN-VALIDATE- RECORD trigger.
Example:
PROCEDURE CHECK_UNIQUE(X_ROWID VARCHAR2,
pkey1 type1, pkey2 type2, ...) IS
DUMMY NUMBER;
BEGIN
SELECT COUNT(1)
INTO DUMMY
FROM table
WHERE pkeycol1 = pkey1
AND pkeycol2 = pkey2
...
AND ((X_ROWID IS NULL) OR (ROWID != X_ROWID));
IF (DUMMY >= 1) then
FND_MESSAGE.SET_NAME('prod', 'message_name');
APP_EXCEPTION.RAISE_EXCEPTION;
END IF;
END CHECK_UNIQUE;
Create your item handler procedure as follows:
PACKAGE BODY block IS
PROCEDURE item(EVENT VARCHAR2) IS
BEGIN
IF (EVENT = 'WHEN-VALIDATE-ITEM') THEN
table_PKG.CHECK_UNIQUE(:block.row_id,
:block.pkey1, :block.pkey2, ...);
ELSE
message('Invalid event in block.item');
END IF
END item;
END block;
When deleting a record, you must be concerned about the existence of other records that may be referencing that record. For example, if an item has already been placed on a Purchase Order, what should occur when you attempt to delete the item? Three possible answers are:
Don't allow the item to be deleted.
Also delete the Purchase Order.
Allow the item to be deleted, and null out the reference to it on the Purchase Order.
Most of the time, the first solution is both the most practical and sensible. To do this, create a procedure that detects these referenced cases, and raise an exception.
To give a warning when detail records will be deleted, create CHECK_REFERENCES as a function which returns FALSE if detail records exist (CHECK_REFERENCES should still raise an exception if deleting the row would cause a referential integrity error).
If a table contains subtypes, you must decide whether you need one CHECK_REFERENCES procedure or one CHECK_REFERENCES procedure per subtype.
If the subtypes share most of the foreign key references with some subtype-specific foreign key references, then create just one CHECK_REFERENCES procedure with the first parameter a subtype discriminator.
If the subtypes are orthogonal, then create a CHECK_subtype_REFERENCES procedure for each subtype.
Create your table handler procedures as follows:
CREATE OR REPLACE PACKAGE BODY table_PKG AS
PROCEDURE CHECK_REFERENCES(pkey1 type1, pkey2 type2, ...) IS
MESSAGE_NAME VARCHAR2(80);
DUMMY credit;
BEGIN
MESSAGE_NAME := 'message_name1';
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM referencing_table1
WHERE ref_key1 = pkey1
AND ref_key2 = pkey2
...
);
MESSAGE_NAME := 'message_name2';
SELECT 1 INTO DUMMY FROM DUAL WHERE NOT EXISTS
(SELECT 1 FROM referencing_table2
WHERE ref_key1 = pkey1
AND ref_key2 = pkey2
...
);
...
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_MESSAGE.SET_NAME('prod', MESSAGE_NAME);
APP_EXCEPTION.RAISE_EXCEPTION;
END CHECK_REFERENCES;
END table_PKG;
Create your event handler procedures as follows:
PACKAGE BODY block IS
PROCEDURE key_delete IS
BEGIN
--
-- First make sure its possible to delete this record.
-- An exception will be raised if its not.
--
table_PKG.CHECK_REFRENCES(pkey1, pkey2, ...);
--
-- Since it is possible to delete the row, ask the
-- user if they really want to,
-- and delete it if they respond with 'OK'.
--
app_record.delete_row;
END key_delete;
END block;
Call the event handler:
Trigger: KEY-DELETE:
block.dey_delete;
Suggestion: You should do similar steps again with the ON-DELETE trigger. It is possible that between the time a user requested the delete, and actually saved the transaction, a record was entered elsewhere that will cause referential integrity problems. Remember that KEY-DELETE fires in response to the user initiating a delete, but it does not actually perform the delete; it just flags the record to be deleted and clears it from the screen. The ON-DELETE trigger fires at commit time and actually performs the delete.