Forum Stats

  • 3,768,513 Users
  • 2,252,804 Discussions
  • 7,874,602 Comments

Discussions

TRIGGER PROBLEM

Josxlu
Josxlu Member Posts: 11
edited May 23, 2020 8:11AM in SQL & PL/SQL

Hello everyone!

I'm having problems implementing triggers in my database. It is a project for my University and i'm trying to develope a Library with books, copies, librarian, orders, suppliers...

This is a OR database (Object-Relation) problem.

I'm going to leave you here my table's code in case you wanna help me you can see the code:

CREATE TABLE Miembros_objtab OF Miembro_objtyp (

            DNI PRIMARY KEY,             

);

CREATE TYPE Alumno_objtyp UNDER Miembro_objtyp (

            carrera VARCHAR2 (10),

);

CREATE TYPE Profesor_objtyp UNDER Miembro_objtyp (

            edificio VARCHAR2 (10),

);

CREATE TYPE Ejemplar_objtyp AS OBJECT (

            num_registro NUMBER(5),

            edicion NUMBER(5),

            estado NUMBER(1),

);

CREATE TABLE Ejemplar_objtab OF Ejemplar_objtyp (

            num_registro PRIMARY KEY,

            CHECK (estado IN (0,1)),

            estado NOT NULL,

            edicion NOT NULL

);

CREATE OR REPLACE TYPE Prestamo_objtyp AS OBJECT (

            Num_prestamo NUMBER(5),

            fecha_expiracion DATE,

            fecha_adquisición DATE,

            fecha_devolución DATE,

            bibliotecario REF Bibliotecario_objtyp,

            miembro REF Miembro_objtyp,

            ejemplar REF Ejemplar_objtyp,

);

CREATE TABLE Prestamo_objtab OF Prestamo_objtyp (

            Num_prestamo PRIMARY KEY,

            CHECK (duracion < 16),

            bibliotecario SCOPE IS Bibliotecario_objtab,

            miembro SCOPE IS Miembros_objtab,

            ejemplar SCOPE IS Ejemplar_objtab

);

CREATE TYPE Registro_objtyp AS OBJECT (

            biblioteca REF Biblioteca_objtyp,

            ejemplar REF Ejemplar_objtyp

);

CREATE TYPE Registro_ntabtyp AS TABLE OF Registro_objtyp;

CREATE TYPE Miembro_objtyp AS OBJECT (

            DNI VARCHAR2 (9),

            num_miembro NUMBER (5),

) NOT FINAL NOT INSTANTIABLE;

CREATE TYPE Bibliotecario_objtyp AS OBJECT (

            DNI VARCHAR2(9),

            Id_empleado NUMBER(5),

);

CREATE TABLE Bibliotecario_objtab OF Bibliotecario_objtyp(

            DNI PRIMARY KEY,

            Id_empleado UNIQUE NOT NULL

);

CREATE TYPE Suministro_objtyp AS OBJECT (

            precio NUMBER(6,2),

            proveedor REF Proveedor_objtyp,

);

CREATE TYPE Suministro_ntabtyp AS TABLE OF Suministro_objtyp;

CREATE TYPE Libro_objtyp AS OBJECT (

            ISBN NUMBER(10),

            suministro Suministro_ntabtyp,

            registro Registro_ntabtyp

);

CREATE TABLE Libro_objtab OF Libro_objtyp(

            ISBN PRIMARY KEY

)

    NESTED TABLE suministro STORE AS Suministro_ntab

    NESTED TABLE registro STORE AS Registro_ntab;

ALTER TABLE Suministro_ntab ADD (CHECK (precio>0 and descuento>0),

SCOPE FOR (Proveedor) IS Proveedor_objtab);

CREATE TYPE LineaPedido_objtyp AS OBJECT (

     Id_lpedido NUMBER(5),

     libro REF Libro_objtyp,

     bibliotecario REF Bibliotecario_objtyp

);

CREATE TYPE LineaPedido_ntabtyp AS TABLE OF LineaPedido_objtyp;

CREATE TYPE Pedido_objtyp AS OBJECT (

     Id_pedido NUMBER(5),

     proveedor REF Proveedor_objtyp,

     lineas LineaPedido_ntabtyp

);

CREATE TABLE Pedido_objtab OF Pedido_objtyp(

            Id_pedido PRIMARY KEY,

            proveedor SCOPE IS Proveedor_objtab)

NESTED TABLE lineas STORE AS LineaPedido_ntab;

ALTER TABLE LineaPedido_ntab ADD (

SCOPE FOR (bibliotecario) IS Bibliotecario_objtab,

SCOPE FOR (libro) IS Libro_objtab);

ALTER TABLE LineaPedido_ntab ADD (

PRIMARY KEY (NESTED_TABLE_ID, Id_lpedido),

CHECK (cantidad > 0));

Okey this is code i have implemented, i have just deleted some attributes in order to give you a code more easy to follow.

The attributes and tables are in spanish (cause im spanish) i translate it to you in order to make you understand it better:

Pedidio = order

Linea_Pedido = row_order

Libro = book

Proveedor = supplier

Suministro = supply

Bibliotecario = Librarian

Prestamo =Loan

Ejemplar = Copy

Miembro = Member

Alumno= Student

Profesor=Proffesor

Registro =Register (this table is used to make a relation between a book and the copies ot has on the library, the copies are the thing that the member loan)

Attributes of prestamo that we will have to use on a trigger:

     fecha devolucion=return_date (when the member has returned the copy)

     fecha_adquisicion=acquisition_date (when the member take the copy)

     fecha_expiracion=expiration_date (when the copy its suppose to be returned)

The thing is i am implementing some triggers respect this part of the database and most of the are no working and i dont understand why it is being so complicating to me make it work so i post here two-three of them and if you know how to help me i will be so greatfull.

The first one is this:

1."Check that the Row_orders include a book inside of the book table (Libro_objtab), whenever we try to update or insert(Pedido_objtab)."

This is our try:

CREATE VIEW PEDIDO_TAB AS (SELECT * FROM Pedido_objtab);

UPDATE TABLE (SELECT Lineas FROM Pedido_objtab WHERE ID_PEDIDO=1) SET

LIBRO=(SELECT REF(L) FROM Libro_objtab L where ISBN=1234567891)

where id_lpedido=2;

CREATE TRIGGER comprobar_lineaspedido

INSTEAD OF INSERT OR UPDATE OF Lineas ON Pedido_objtab

FOR EACH ROW

BEGIN

SELECT COUNT(*) INTO NUM

FROM LIBRO_OBJTAB, TABLE(SUMINISTRO) S

WHERE REF(L)=:NEW.LIBRO AND :PARENT.PROVEEDOR=S.PROVEEDOR;

IF NUM>0 THEN

   

    IF UPDATING THEN

        UPDATE PEDIDO_OBJTAB SET ID_PEDIDO=:NEW.ID_PEDIDO,fecha_realizacion=:NEW.fecha_realizacion,

fecha_entrega=:NEW.fecha_entrega,precio_total=:New.precio_total,proveedor=:NEW.proveedor,urgencia=:NEW.urgencia,LINEAS=:NEW.LINEAS

        WHERE ID_PEDIDO= :PARENT.ID_PEPIDO;

    END IF;

   

    IF INSERTING THEN

        INSERT INTO (SELECT * FROM PEDIDO_OBJTAB WHERE ID_PEDIDO=:PARENT.ID_PEDIDO) VALUES (:NEW.ID_PEDIDO,

        :NEW.fecha_realizacion,:NEW.fecha_entrega,:NEW.precio_total,:NEW.urgencia,:NEW.proveedor,:NEW.lineas);

    END IF;

   

ELSE DBMS_OUTPUT.PUT_LINE('No se encuentra el libro que se quiere añadir a la linea de pedido');

END IF;

END;

This one is not working and we don't understand why, the error that comes out is that we have to give a value for PARENT and that doesn't request any value.

The attributes that are being updating with the :new i have deleted it from the tables, like i said before to make you more easy to understand the scheme of the problem.

The second one is this:

2."The system imposse a penalitation when the book exceeds the return_date. It means expiration_date<return_date":

UPDATE Prestamo_objtab SET fecha_devolución=SYSDATE WHERE Num_prestamo=2545;

CREATE OR REPLACE TRIGGER penalizacion

INSERT OR UPDATE OF fecha_devolución ON Prestamo_objtab

COMPOUND TRIGGER

numero INTEGER;

Type tnum is table of Prestamo_objtab.num_prestamo%type index by integer;

vnum tnum;

Type tfecha is table of Prestamo_objtab.fecha_devolución%type index by date;

vfecha tfecha;

vdevoluciones binary_integer:=0;

BEFORE EACH ROW IS

    vdevoluciones= vdevoluciones + 1;

    vnum(vdevoluciones)= :new.num_prestamo;

    vfecha(vdevoluciones)= :new.fecha_devolución;   

END BEFORE EACH ROW;

AFTER STATEMENT IS

BEGIN

SELECT COUNT(*) INTO numero

FROM Prestamo_objtab pr

WHERE pr.fecha_devolución>pr.fecha_expiracion AND fecha_devolución=SYSDATE;

IF numero>0 THEN

    DBMS_OUTPUT.PUT('El miembro se ha excedido del plazo, ha de ser penalizado');

END IF;

END AFTER STATEMENT;

END penalizacion;

This isn't working neither.

Thanks for reading and i hope it is clear, if you need any type of clarification just tell me, i am aware that it's hard to understand a problem this long and more if it is not on your idiom, and adding to this that my english is not perfect...

Thaks for reading!

Regards

Answers

  • Mike Kutz
    Mike Kutz Member Posts: 5,796 Silver Crown
    edited May 23, 2020 8:11AM

    Here are my Code Review notes (some may be opinionated):

    1) OR is a bad choice

    Unless the use of an OR is required by the project, I would use 3NF design.

    When the Object needs to evolve, your schema becomes a nightmare to manage.  (PITA)

    2) SELECT COUNT(*)

    You need to acquire a lock before you can COUNT(*)  rows.  Otherwise, two different sessions could produce the same resulting value.

    Serialization causes Databases to be slow.

    There are other methods to simulate "gap free sequential numbers". One of them is to use a VIEW that calculates the "row number" using ROW_NUMBER().

    3) fecha_devolución=SYSDATE (2nd trigger)

    Oracle DATE includes a time component.  The value for SYSDATE can/will have different values between now and now.  The value the DB uses (for SYSDATE) in the UPDATE statement could be different than the value the DB uses (for SYSDATE) within the trigger.

    4) IF numero>0 THEN (2nd trigger)

    DBMS_OUTPUT doesn't do anything for applications.

    In this case, the DML operation will still succeed.

    Depending on your Workflow of the application, this section should probably RAISE an error or actually apply the appropriate penalization.

    5) What Errors?

    Please post the actual ORA error codes you are receiving.

    Also, please post an example of what you mean by "it doesn't work".

    6) triggers

    For me, I follow an MVC Design. (model-view-control)

    • Model is hidden from the application
    • Data is accessed through a VIEW
    • Control (DML) of the data is done through a Package

    If I need simple CRUD style control, I would create an INSTEAD OF trigger on the VIEW.  This trigger will still call the functions/procedures within the Package.

    My $0.02

    MK