HI ALL,
I CREATED TRIGGER TO CHECK ITEM_NAME BEFORE UPDATING ON TABLE T_ITEMS IF ANOTHER ITEM_NAME LIKE CURRENT ITEM_NAME OR NOT, IF LIKE ANOTHER ITEM_NAME DON'T SAVE CURRENT UPDATED ITEM_NAME.
MY STEPS IS:
1- I CREATED FUNCTION CALL CH_UNIQUE_ITEM_NAME
CREATE OR REPLACE FUNCTION CH_UNIQUE_ITEM_NAME
( P_ID NUMBER, P_ITEM_NAME IN VARCHAR2 )
RETURN NUMBER
AS
X_COUNT NUMBER;
-- FUNCTION "CH_UNIQUE_ITEM_NAME" TO CHECK ITEM_NAME LIKE OTHER ITEM_NAME OR NOT.
BEGIN
SELECT COUNT(ITEM_NAME)
INTO X_COUNT
FROM T_ITEMS
WHERE ID != P_ID
AND UPPER(ITEM_NAME) LIKE UPPER(P_ITEM_NAME);
RETURN X_COUNT;
END CH_UNIQUE_ITEM_NAME;
2- I CREATED TRIGGER CALL TRG_CH_UNIQUE_T_ITEMS
CREATE OR REPLACE TRIGGER "TRG_CH_UNIQUE_T_ITEMS" BEFORE
UPDATE
ON T_ITEMS FOR EACH ROW
DECLARE
X_CHECK NUMBER;
BEGIN
SELECT CH_UNIQUE_ITEM_NAME(:OLD.ID, :NEW.ITEM_NAME) INTO X_CHECK FROM DUAL;
IF (:NEW.ITEM_NAME NOT LIKE :OLD.ITEM_NAME AND X_CHECK > 0)
THEN RAISE_APPLICATION_ERROR (-20007, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
END IF;
END;
NOW WHEN UPDATE ANY ITEMS I FOUND ERROR "ERROR/ TABLE T_ITEMS is mutating, trigger/function may not see it ORA-06512"
PLEASE HELP ME TO SOLVE THIS ERROR . I TIRED TO SOLVE IT ..
THANKS ,