Create a TRIGGER to insert a record in a table

Question

Create a TRIGGER to insert a record in a table

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

I have three tables Detalle_Compra , Detalle_Venta e Inventario :

CREATE TABLE Detalle_Compra
(
Pedido int,
Producto int,
Cantidad int not null,
Precio int,
foreign key (Producto) references Productos,
foreign key (Pedido) references Pedidos,
Primary key (Pedido, Producto)
)
Go

CREATE TABLE Detalle_Venta
(
FacturaV int,
Producto int,
Cantidad int,
Precio int,
Primary Key (FacturaV, Producto),
foreign key (Producto) references Productos,
foreign key (FacturaV) references Facturas_V,
)
Go

CREATE TABLE Inventario
(
Factura int,
Operacion varchar (6) not null,
Producto int,
cantidad int not null,
Observacion Nvarchar (200),
Foreign key (Factura, Producto) references Detalle_Compra,
Foreign key (Factura, Producto) references Detalle_Venta,
Primary key (Factura, Operacion,Producto)
)
Go

I need a TRIGGER to make a record in the Inventario table when a record is made in the table Detalle_Compra and / or Detalle_Venta .

I already tried to do it but this one just lets me do an insert and then I get the message:

INSERT statement in conflict with the FOREIGN KEY constraint "FK__Inventory__32E0915F". The conflict has appeared in the database "Inventario_Erikar", table "dbo.Detalle_Compra".

CREATE TRIGGER DIS_InventarioVenta ON Detalle_Venta
FOR INSERT
AS
DECLARE @Factura int
DECLARE @Operacion varchar (6)
DECLARE @Producto int
DECLARE @cantidad int

SELECT @Factura = i.FacturaV  FROM Detalle_Venta i;
SELECT @Producto = i.Producto FROM Detalle_Venta i;
SELECT @cantidad = i.Cantidad  FROM Detalle_Venta i;

INSERT INTO inventario (Factura, Operacion,Producto,cantidad,Observacion)
    VALUES (@Factura,'', @Producto,@cantidad,'')
GO

sql-server trigger

asked by Dilan Perez L 08.09.2017 в 00:36

source

2 answers

0

What I would try first is to change the select to get the newly inserted data:

SELECT @Factura = i.FacturaV, @Producto = i.Producto, @cantidad = i.Cantidad  FROM inserted i;

Although in reality the problem is that you are trying to insert a non-existent product or order in detail_compra, but the reason may be the fact that the assignment of the variables you are doing directly against the table and not against the newly inserted record, which can give unexpected results.

answered by 08.09.2017 в 14:06

Avoid the use of if and hardcoded string Errors when creating SQL DB

score 1 · Accepted Answer

The problem is simple, you are inserting in Inventario an operation that does not have its counterpart in Detalle_Compra . Besides that you have other errors that make it difficult to deepen any analysis, let's see:

First of all: Foreign key (Factura, Producto) references Detalle_Compra , is wrong because there is no field Invoice in Detalle_Compra I understand that it could actually become like this: Primary key (Pedido, Producto) (or could it be FacturaC ?). Also in Foreign key (Factura, Producto) references Detalle_Venta you have a problem of the same type, in the table Detalle_Venta the column Factura you call FacturaV , on the other hand if eventually you want the two FK in inventory, you should allow factura or Pedido be "nullable" thing that you could not the PK like this: Primary key (Factura, Operacion, Producto) . I imagine that these are all typographical errors, I suggest you review this and edit the question as it is difficult to understand the model with these faults.

Beyond the comments, one possibility is to define Inventario in the following way

CREATE TABLE Inventario
(
    Pedido int null,
    Factura int null,
    Operacion varchar (6) not null,
    Producto int,
    cantidad int not null,
    Observacion Nvarchar (200),
    Foreign key (Pedido, Producto) references Detalle_Compra,
    Foreign key (Factura, Producto) references Detalle_Venta,
    -- Primary key (Factura, Operacion,Producto)
)
Go

Changes:

We add Pedido to the table (Reference to purchases)
We made that Pedido and Factura are "nullable", it has to be so because I understand that either insert from a purchase or from a sale, that is or we will have Factura or Pedido but never both at the same time.
By making these fields "nullable" we had to comment on the Primary Key, you should look for other columns to do PK for example add a column IDENTITY .