Oracle对象表触发器开发求助：禁止用户添加自身为好友并实现双向好友关系

Hey Alicia, let's break down what's going wrong with your triggers and fix them step by step. You've got two main issues here: allowing self-friend additions, and the "too many values" error from your updated trigger. Let's tackle them one by one.

1. Why You Could Add Yourself as a Friend #

Your original nested table trigger Act_NoMismoAmigo_N didn't include any validation to check if the friend being added was the user themselves. The line WHERE id_usuario != :PARENT.id_usuario was misplaced—it wasn't verifying the actual friend reference, just incorrectly updating other users' friend counts.

2. Fixing the "Too Many Values" Error #

In your updated trigger, you tried inserting all of the user's column values into the nested table, but AmigosList is a table of REF Usuario_ObjTyp—you only need to insert the reference to the user, not every attribute of their object. That mismatch between expected and provided values caused the error.

3. Corrected Trigger Implementation #

Let's rewrite the triggers to meet all your requirements:

• Block self-friend additions
• Automatically create bidirectional friend relationships
• Maintain the ct_amigos count accurately

Nested Table Trigger (Handles Validation & Bidirectional Links) #

This trigger will manage individual friend additions, block self-references, and create the reverse friend link automatically:

CREATE OR REPLACE TRIGGER Act_NoMismoAmigo_N
INSTEAD OF INSERT ON NESTED TABLE AmigosList OF Usuarios
FOR EACH ROW
DECLARE
    v_friend_id NUMBER;
    v_self_ref REF Usuario_ObjTyp;
BEGIN
    -- Extract the ID of the friend being added from their REF
    SELECT DEREF(:NEW.COLUMN_VALUE).id_usuario INTO v_friend_id FROM DUAL;
    
    -- Get the reference to the parent user (the one adding the friend)
    SELECT REF(u) INTO v_self_ref FROM Usuario_ObjTab u WHERE u.id_usuario = :PARENT.id_usuario;
    
    -- Block self-friend addition
    IF v_friend_id = :PARENT.id_usuario THEN
        RAISE_APPLICATION_ERROR(-20111, 'No puedes añadirte a ti mismo como amigo');
    END IF;
    
    -- Add the friend to the parent user's AmigosList
    INSERT INTO TABLE (SELECT AmigosList FROM Usuario_ObjTab WHERE id_usuario = :PARENT.id_usuario)
    VALUES (:NEW.COLUMN_VALUE);
    
    -- Update parent user's friend count
    UPDATE Usuario_ObjTab
    SET ct_amigos = ct_amigos + 1
    WHERE id_usuario = :PARENT.id_usuario;
    
    -- Add parent user to the friend's AmigosList (bidirectional link)
    INSERT INTO TABLE (SELECT AmigosList FROM Usuario_ObjTab WHERE id_usuario = v_friend_id)
    VALUES (v_self_ref);
    
    -- Update friend's friend count
    UPDATE Usuario_ObjTab
    SET ct_amigos = ct_amigos + 1
    WHERE id_usuario = v_friend_id;
END;
/

Main Table Trigger (Validates Full List Updates) #

This simplified trigger ensures that if someone modifies the entire AmigosList directly (instead of adding individual entries), it still blocks self-references and syncs the ct_amigos count:

CREATE OR REPLACE TRIGGER Act_Validate_AmigosList
BEFORE INSERT OR UPDATE OF AmigosList ON Usuario_ObjTab
FOR EACH ROW
DECLARE
    v_self_ref REF Usuario_ObjTyp;
    v_friend_id NUMBER;
BEGIN
    SELECT REF(:NEW) INTO v_self_ref FROM DUAL;
    
    IF :NEW.AmigosList IS NOT NULL THEN
        FOR i IN 1..:NEW.AmigosList.COUNT LOOP
            -- Extract ID from each friend reference in the list
            SELECT DEREF(:NEW.AmigosList(i)).id_usuario INTO v_friend_id FROM DUAL;
            
            IF v_friend_id = :NEW.id_usuario THEN
                RAISE_APPLICATION_ERROR(-20112, 'La lista de amigos no puede incluirte a ti mismo');
            END IF;
        END LOOP;
        
        -- Sync friend count to match the list size
        :NEW.ct_amigos := :NEW.AmigosList.COUNT;
    ELSE
        :NEW.ct_amigos := 0;
    END IF;
END;
/

4. Testing the Fix #

If you try to add yourself as a friend now:

INSERT INTO TABLE (SELECT AmigosList FROM Usuario_ObjTab WHERE id_usuario = 3 )
VALUES ((SELECT REF(U) FROM Usuario_ObjTab U WHERE id_usuario = 3));

You'll get the error ORA-20111: No puedes añadirte a ti mismo como amigo—exactly what we want.

For a valid insertion (user 3 adding user 5):

INSERT INTO TABLE (SELECT AmigosList FROM Usuario_ObjTab WHERE id_usuario = 3 )
VALUES ((SELECT REF(U) FROM Usuario_ObjTab U WHERE id_usuario = 5));

This will:

1. Add user 5 to user 3's AmigosList
2. Increment user 3's ct_amigos by 1
3. Add user 3 to user 5's AmigosList
4. Increment user 5's ct_amigos by 1

5. Key Notes #

• We use DEREF() to convert a REF into the actual object, so we can compare user IDs.
• The nested table trigger handles bidirectional links automatically—no need to manually insert both ways.
• The main table trigger covers edge cases where someone updates the entire friend list at once.

内容的提问来源于stack exchange，提问作者Alicia Fuentes