Trigger with compilation errors

0

Well it turns out that I want to make a trigger that when I update a column in a specific table records are annexed ie I have a table where articles when the sale is made and the quantity is discounted that information is given to a sales table. acontinuacion show the code:

create table articulos( 
id_articulo number(5) primary key,
nombre varchar2(32),
detalle varchar2(32),
precio number(8) not null,
cantidad number(10) not null,
);
create table ventas(
    id_venta number(5) primary key,
    id_articulo number(5) references articulos,
    id_empleado number(6) references empleado, 
    id_cliente  number(5) references cliente, 
    cantidad number(10) not null,
    precio number(8) not null,
    total number(8) not null,
    fecha_venta date
); 

The trigger is as follows:

 create sequence id_venta
start with 1
increment by 1
order;

create or replace trigger anexventa
before update on articulos
referencing new as new old as old
for each row
declare
x number;
begin
select id_venta.nextval into x from dual;
 :new.id_venta := x;
if updating('cantidad') then
insert into  ventas values(:NEW.id_venta, :old.id_articulo, :old.id_empleado, :old.id_cliente, :NEW.cantidad, :NEW.precio, :NEW.total, sysdate);
end if;
end anexventa;
/

As you can see, attach a sequence which allows me to generate the sale id automatically.

The errors thrown at me are:

SQL> show errors trigger anexventa;
Errors for TRIGGER ANEXVENTA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/2      PLS-00049: bad bind variable 'NEW.ID_VENTA'
7/28     PLS-00049: bad bind variable 'NEW.ID_VENTA'
7/61     PLS-00049: bad bind variable 'OLD.ID_EMPLEADO'
7/79     PLS-00049: bad bind variable 'OLD.ID_CLIENTE'
7/124    PLS-00049: bad bind variable 'NEW.TOTAL'

I hope someone can guide me regarding the problem. Greetings.

    
asked by David Alfonso 17.12.2017 в 21:36
source

1 answer

0

If you define a trigger for table articulos :

before update on articulos

... then obviously, you will only have access to the columns of that table with :OLD.xxx or :NEW.xxx .

In your case, you are trying to access columns in the venta table in the trigger for the articulos table. That makes no sense. Limit yourself to using the columns in the articulos table.

    
answered by 17.12.2017 в 21:49