oracle - 用于检查用户不能将自己添加为朋友的触发器

问题描述

我有一个表（Usuario）和一个嵌套表（Amigos），还有一个类型（AmigosList）。这些是这些表的结构：

CREATE OR REPLACE TYPE Usuario_ObjTyp AS OBJECT (
    id_usuario NUMBER(8),
    nick VARCHAR2(20),
    nombre VARCHAR(20),
    apellido VARCHAR(20),
    email VARCHAR(30),
    contrasena VARCHAR(30),
    ct_amigos NUMBER(4),
    direccion VARCHAR2(30),
    localidad VARCHAR2(20),
    cp VARCHAR2(7),
    pais VARCHAR2(20),
    telefono VARCHAR2(10),
    Tarjetas REF Tarjeta_Credito_ObjTyp,
    PayPal_Usuario REF Paypal_ObjTyp,
   MAP MEMBER FUNCTION Get_Nick RETURN VARCHAR2
); 
CREATE OR REPLACE TYPE Amigos_ntabtyp IS TABLE OF REF Usuario_ObjTyp;

我也有嵌套表的这个类型，用于触发器

ALTER TYPE Usuario_ObjTyp ADD ATTRIBUTE AmigosList Amigos_ntabtyp CASCADE;

情况是，每次我想向用户添加朋友或修改它时，它都需要检查该朋友是否不是用户本身。另外，比如我们有user1和user2，当user1和user2交朋友时，需要检查user1没有添加自己，然后在user1的AmigosList中添加user2，在user2的AmigosList列表中添加用户 1。

我尝试的是这个（我真的很困惑如何制作它）：注意：-VCA：用户拥有的朋友数量。-ct_amigos：用户拥有的朋友数量。

--for the main table
CREATE OR REPLACE TRIGGER Act_NoMismoAmigo
    INSTEAD OF INSERT OR UPDATE ON Usuarios
    FOR EACH ROW
    DECLARE
        VCA NUMBER;
        I BINARY_INTEGER;
    BEGIN
        IF :NEW.AmigosList IS NOT NULL THEN
            VCA := 0;
            FOR I IN 1..:NEW.AmigosList.COUNT LOOP
                VCA := VCA + 1;
            END LOOP;
        ELSE
            VCA := 0;
        END IF;

        IF INSERTING THEN
            INSERT INTO USUARIO_OBJTAB
            VALUES (:NEW.id_usuario, :NEW.nick, :NEW.nombre, :NEW.apellido, :NEW.email, :NEW.contrasena, VCA, :NEW.direccion, :NEW.localidad, :NEW.cp, :NEW.pais, :NEW.telefono, :NEW.tarjetas, :NEW.PayPal_Usuario, :NEW.AmigosList);
        END IF;
        IF UPDATING THEN
            UPDATE USUARIO_OBJTAB SET id_usuario = :NEW.id_usuario, nick = :NEW.nick, nombre = :NEW.nombre, apellido = :NEW.apellido, email = :NEW.email, contrasena = :NEW.contrasena, ct_amigos = VCA, direccion = :NEW.direccion, localidad = :NEW.localidad, cp = :NEW.cp, pais = :NEW.pais, telefono = :NEW.telefono, Tarjetas = :NEW.tarjetas, Paypal_Usuario = :NEW.PayPal_Usuario, AmigosList = :NEW.AmigosList
            WHERE id_usuario = :OLD.id_usuario;
        END IF;
    END;

--for nested table
CREATE OR REPLACE TRIGGER Act_NoMismoAmigo_N
    INSTEAD OF INSERT OR UPDATE
    ON NESTED TABLE AmigosList OF Usuarios
    FOR EACH ROW
    BEGIN
        IF INSERTING THEN
            UPDATE USUARIO_OBJTAB 
                SET ct_amigos = ct_amigos + 1
                WHERE id_usuario != :PARENT.id_usuario;     -- Check if its not the same user?
            INSERT INTO TABLE (SELECT AmigosList FROM USUARIO_OBJTAB WHERE id_usuario = :PARENT.id_usuario)
                VALUES (:NEW.COLUMN_VALUE);
        END IF;

        IF UPDATING THEN
            UPDATE USUARIO_OBJTAB
                SET ct_amigos = ct_amigos --if we delete and add another friend it sets the same
                WHERE id_usuario = :PARENT.id_usuario;
            UPDATE TABLE (SELECT AmigosList FROM USUARIO_OBJTAB WHERE id_usuario != :PARENT.id_usuario) 
                SET COLUMN_VALUE = :NEW.COLUMN_VALUE;
        END IF;
    END;

我尝试过的插入是：

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

我从插入中得到的结果是它作为朋友添加到用户本身（这是不正确的）。

也许我只是困惑，但我被困在那里。请帮助我，非常感谢。

--UPDATE 我试过了，仍然给我一个错误，即 INSERT INTO TABLE(SELECT AmigosList FROM USUARIO_OBJTAB u WHERE REF(u)=:new.AmigosList(I)) values (:NEW.id_usuario, :NEW.尼克, :NEW.nombre, :NEW.apellido, :NEW.email, :NEW.contrasena, :NEW.CT_AMIGOS, :NEW.direccion, :NEW.localidad, :NEW.cp, :NEW.pais, :NEW。电话，：NEW.tarjetas，：NEW.PayPal_Usuario，：NEW.AmigosList）；结束循环；但我不知道为什么。这是主表中的触发器：

create or replace TRIGGER Act_NoMismoAmigo
    INSTEAD OF INSERT OR UPDATE OR DELETE ON Usuarios
    FOR EACH ROW
    DECLARE
        I BINARY_INTEGER;
        VUSER REF USUARIO_OBJTYP;
    BEGIN
      Select ref(a) into VUSER from usuario_objtab a where id_usuario = :new.id_usuario;
      --if el id del nuevo usuario es distinto al de nuestro usuario: se añade a su AmigosList
        IF :NEW.AmigosList IS NOT NULL THEN
            FOR I IN 1..:NEW.AmigosList.COUNT LOOP
                IF :new.AmigosList(I) = VUSER THEN
                   raise_application_error(-20111, 'No se puede añadir, coincide');
                END IF;     
            END LOOP;
           --insert lista/actualizacion
            FOR I IN 1..:NEW.AmigosList.COUNT LOOP
                 INSERT INTO TABLE(SELECT AmigosList FROM USUARIO_OBJTAB u WHERE REF(u)=:new.AmigosList(I)) values (:NEW.id_usuario, :NEW.nick, :NEW.nombre, 
                 :NEW.apellido, :NEW.email, :NEW.contrasena, :NEW.CT_AMIGOS, :NEW.direccion, :NEW.localidad, :NEW.cp, :NEW.pais, :NEW.telefono, :NEW.tarjetas, :NEW.PayPal_Usuario, :NEW.AmigosList);
            END LOOP;
           
           
            --actualizacion/borrado lista 
            FOR I IN 1..:NEW.AmigosList.COUNT LOOP
                 DELETE FROM TABLE (SELECT AmigosList FROM USUARIO_OBJTAB u WHERE REF(u)=:OLD.AmigosList(I)) WHERE COLUMN_VALUE = VUSER;
            END LOOP;
        END IF;
    END;

标签： oracleplsqltriggers

只是一个小想法。这是未经测试的代码，也许它可以帮助，也许没有。我稍微调整了你的第一个触发器：

--for the main table
CREATE OR REPLACE TRIGGER Act_NoMismoAmigo
    INSTEAD OF INSERT OR UPDATE ON Usuarios
    FOR EACH ROW
    DECLARE
        i BINARY_INTEGER;
    BEGIN
    
        IF :NEW.AmigosList IS NOT NULL THEN
            FOR i IN :NEW.AmigosList.FIRST..:NEW.AmigosList.LAST LOOP
                IF :NEW.AmigosList(i).id_usuario = :NEW.id_usuario THEN
                    :NEW.AmigosList.DELETE(i); -- Remove self-reference from list - user can't be friend of itself
                END IF;
            END LOOP;
            
            :NEW.AmigosList := SET (:NEW.AmigosList); -- Make the list distinct
            :NEW.ct_amigos := :NEW.AmigosList.COUNT;
        ELSE
            :NEW.ct_amigos := 0;
        END IF;
    
        IF INSERTING THEN
            INSERT INTO USUARIO_OBJTAB
            VALUES (:NEW.id_usuario, :NEW.nick, :NEW.nombre, :NEW.apellido, :NEW.email, :NEW.contrasena, :NEW.ct_amigos, :NEW.direccion, :NEW.localidad, :NEW.cp, :NEW.pais, :NEW.telefono, :NEW.tarjetas, :NEW.PayPal_Usuario, :NEW.AmigosList);
        END IF;
        IF UPDATING THEN
            UPDATE USUARIO_OBJTAB SET id_usuario = :NEW.id_usuario, nick = :NEW.nick, nombre = :NEW.nombre, apellido = :NEW.apellido, email = :NEW.email, contrasena = :NEW.contrasena, ct_amigos = VCA, direccion = :NEW.direccion, localidad = :NEW.localidad, cp = :NEW.cp, pais = :NEW.pais, telefono = :NEW.telefono, Tarjetas = :NEW.tarjetas, Paypal_Usuario = :NEW.PayPal_Usuario, AmigosList = :NEW.AmigosList
            WHERE id_usuario = :OLD.id_usuario;
        END IF;     
    END;

oracle - 用于检查用户不能将自己添加为朋友的触发器

问题描述

解决方案

推荐阅读