TRIGGER

Hola a todos, soy principiante en todo esto, me gustaria si alguien puede
aclarame esta duda. Intento Extraer datos de una tabla mediante un TRIGGER
pero no puedo hacerlo trabajar les expongo lo que estoy usando esperando em
puedan ayudar. gracias.

Alter Trigger InvoicesExchange
On [Dbo].[Factura]
After Insert, Update, Delete

Declare @IdInvoice As Varchar (20)
Declare @IdOrder Varchar (20)
Declare @DateInvoice DateTime (8)
Declare @PaymentDay DateTime (8)
Declare @TotalAmount Money (8)
Declare @ShippingCost Money (8)
Declare @RemainderAmount Money (8)
Declare @ShipmentStatus Int (4)
Declare @PaidInvoice Bit (1)
Declare @PaidCommission Bit(1)
Declare @ArrivalDate DateTime (8)
Declare @IdTransport SmallInt (2)
Declare @TransactionType Int (4)
Declare @TimeStamp DateTime (8)

Select @IdInvoice = (Select K_Factura From Inserted)
Select @IdOrder = (Select K_Pedido From Inserted)
Select @DateInvoice = (Select F_Factura From Inserted)
Select @PaymentDay = (Select F_Vencimiento From Inserted)
Select @TotalAmount = (Select Monto From Inserted)
Select @ShippingCost = (Select Flete From Intserted)
Select @RemainderAmount = (Select Saldo From Inserted)
Select @ShipmentStatus = (Select Estatus_Envio From Inserted)
Select @PaidInvoice = (Select Pagada From Inserted)
Select @PaidCommission = (Select Comision_Pagada From Inserted)
Select @ArrivalDate = (Select F_Llegada_Aduana From Inserted)
Select @IdTransport = (Select K_Trasnporte From Inserted)

Begin
Insert InTo InvoiceExchange(IdInvoice, IdOrder, DateInvoice, PaymentDay,
TotalAmount, ShippingCost, RemainderAmount, ShipmentStatus,
PaidInvoice, PaidCommission, ArrivalDate, IdTransport)

Values (@IdInvoice, @IdOrder, @DateInvoice, @PaymentDay, @TotalAmount,
@ShippingCost, @RemainderAmount, @ShipmentStatus, @PaidInvoice,
@PaidCommission, @ArrivalDate, @IdTransport)
End

A ver, pongamonos de acuerdo

Un trigger solo trabaja en el momento para el cual fue creado, en tu caso,
para un DELETE, INSERT o UPDATE, ¿De acuerdo?

Pero, estas insertando en la misma tabla !!!!

¿Que es lo que quieres hacer exactamente?
Saludos
IIslas


"GAEJ" wrote:

> Hola a todos, soy principiante en todo esto, me gustaria si alguien puede
> aclarame esta duda. Intento Extraer datos de una tabla mediante un TRIGGER
> pero no puedo hacerlo trabajar les expongo lo que estoy usando esperando em
> puedan ayudar. gracias.
>
> Alter Trigger InvoicesExchange
> On [Dbo].[Factura]
> After Insert, Update, Delete
>
> Declare @IdInvoice As Varchar (20)
> Declare @IdOrder Varchar (20)
> Declare @DateInvoice DateTime (8)
> Declare @PaymentDay DateTime (8)
> Declare @TotalAmount Money (8)
> Declare @ShippingCost Money (8)
> Declare @RemainderAmount Money (8)
> Declare @ShipmentStatus Int (4)
> Declare @PaidInvoice Bit (1)
> Declare @PaidCommission Bit(1)
> Declare @ArrivalDate DateTime (8)
> Declare @IdTransport SmallInt (2)
> Declare @TransactionType Int (4)
> Declare @TimeStamp DateTime (8)
>
> Select @IdInvoice = (Select K_Factura From Inserted)
> Select @IdOrder = (Select K_Pedido From Inserted)
> Select @DateInvoice = (Select F_Factura From Inserted)
> Select @PaymentDay = (Select F_Vencimiento From Inserted)
> Select @TotalAmount = (Select Monto From Inserted)
> Select @ShippingCost = (Select Flete From Intserted)
> Select @RemainderAmount = (Select Saldo From Inserted)
> Select @ShipmentStatus = (Select Estatus_Envio From Inserted)
> Select @PaidInvoice = (Select Pagada From Inserted)
> Select @PaidCommission = (Select Comision_Pagada From Inserted)
> Select @ArrivalDate = (Select F_Llegada_Aduana From Inserted)
> Select @IdTransport = (Select K_Trasnporte From Inserted)
>
> Begin
> Insert InTo InvoiceExchange(IdInvoice, IdOrder, DateInvoice, PaymentDay,
> TotalAmount, ShippingCost, RemainderAmount, ShipmentStatus,
> PaidInvoice, PaidCommission, ArrivalDate, IdTransport)
>
> Values (@IdInvoice, @IdOrder, @DateInvoice, @PaymentDay, @TotalAmount,
> @ShippingCost, @RemainderAmount, @ShipmentStatus, @PaidInvoice,
> @PaidCommission, @ArrivalDate, @IdTransport)
> End

Gracias Isaias, Intento Extraer lso datos de la tabla factura cuando esta se
vea afectada a traves del regristro llave K_Factura y envie esa informacion a
la tabla InvoiceExchange.

"Isaias" wrote:

> A ver, pongamonos de acuerdo
>
> Un trigger solo trabaja en el momento para el cual fue creado, en tu caso,
> para un DELETE, INSERT o UPDATE, ¿De acuerdo?
>
> Pero, estas insertando en la misma tabla !!!!
>
> ¿Que es lo que quieres hacer exactamente?
> Saludos
> IIslas
>
>
> "GAEJ" wrote:
>
> > Hola a todos, soy principiante en todo esto, me gustaria si alguien puede
> > aclarame esta duda. Intento Extraer datos de una tabla mediante un TRIGGER
> > pero no puedo hacerlo trabajar les expongo lo que estoy usando esperando em
> > puedan ayudar. gracias.
> >
> > Alter Trigger InvoicesExchange
> > On [Dbo].[Factura]
> > After Insert, Update, Delete
> >
> > Declare @IdInvoice As Varchar (20)
> > Declare @IdOrder Varchar (20)
> > Declare @DateInvoice DateTime (8)
> > Declare @PaymentDay DateTime (8)
> > Declare @TotalAmount Money (8)
> > Declare @ShippingCost Money (8)
> > Declare @RemainderAmount Money (8)
> > Declare @ShipmentStatus Int (4)
> > Declare @PaidInvoice Bit (1)
> > Declare @PaidCommission Bit(1)
> > Declare @ArrivalDate DateTime (8)
> > Declare @IdTransport SmallInt (2)
> > Declare @TransactionType Int (4)
> > Declare @TimeStamp DateTime (8)
> >
> > Select @IdInvoice = (Select K_Factura From Inserted)
> > Select @IdOrder = (Select K_Pedido From Inserted)
> > Select @DateInvoice = (Select F_Factura From Inserted)
> > Select @PaymentDay = (Select F_Vencimiento From Inserted)
> > Select @TotalAmount = (Select Monto From Inserted)
> > Select @ShippingCost = (Select Flete From Intserted)
> > Select @RemainderAmount = (Select Saldo From Inserted)
> > Select @ShipmentStatus = (Select Estatus_Envio From Inserted)
> > Select @PaidInvoice = (Select Pagada From Inserted)
> > Select @PaidCommission = (Select Comision_Pagada From Inserted)
> > Select @ArrivalDate = (Select F_Llegada_Aduana From Inserted)
> > Select @IdTransport = (Select K_Trasnporte From Inserted)
> >
> > Begin
> > Insert InTo InvoiceExchange(IdInvoice, IdOrder, DateInvoice, PaymentDay,
> > TotalAmount, ShippingCost, RemainderAmount, ShipmentStatus,
> > PaidInvoice, PaidCommission, ArrivalDate, IdTransport)
> >
> > Values (@IdInvoice, @IdOrder, @DateInvoice, @PaymentDay, @TotalAmount,
> > @ShippingCost, @RemainderAmount, @ShipmentStatus, @PaidInvoice,
> > @PaidCommission, @ArrivalDate, @IdTransport)
> > End

Entonces el trigger, debe ser creado en la tabla FACTURA e insertarse dichos
registros en la tabla InvoiceExchange.

CREATE Trigger trgFactura On [Dbo].[Factura]
FOR Insert, Update, Delete
AS
BEGIN
IF EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO InvoiceExchange
SELECT * FROM DELETED
END
IF EXISTS(SELECT * FROM INSERTED)
BEGIN
INSERT INTO InvoiceExchange
SELECT * FROM INSERTED
END
END

Nota: A menos que tus columnas de la tabla FACTURA, no fueran iguales a las
de InvoiceExchange, entonces deberas cambiar el SELECT * FROM DELETE/INSERTED
con la lista de columnas a ingresar.

Saludos
IIslas


"GAEJ" wrote:

> Gracias Isaias, Intento Extraer lso datos de la tabla factura cuando esta se
> vea afectada a traves del regristro llave K_Factura y envie esa informacion a
> la tabla InvoiceExchange.
>
> "Isaias" wrote:
>
> > A ver, pongamonos de acuerdo
> >
> > Un trigger solo trabaja en el momento para el cual fue creado, en tu caso,
> > para un DELETE, INSERT o UPDATE, ¿De acuerdo?
> >
> > Pero, estas insertando en la misma tabla !!!!
> >
> > ¿Que es lo que quieres hacer exactamente?
> > Saludos
> > IIslas
> >
> >
> > "GAEJ" wrote:
> >
> > > Hola a todos, soy principiante en todo esto, me gustaria si alguien puede
> > > aclarame esta duda. Intento Extraer datos de una tabla mediante un TRIGGER
> > > pero no puedo hacerlo trabajar les expongo lo que estoy usando esperando em
> > > puedan ayudar. gracias.
> > >
> > > Alter Trigger InvoicesExchange
> > > On [Dbo].[Factura]
> > > After Insert, Update, Delete
> > >
> > > Declare @IdInvoice As Varchar (20)
> > > Declare @IdOrder Varchar (20)
> > > Declare @DateInvoice DateTime (8)
> > > Declare @PaymentDay DateTime (8)
> > > Declare @TotalAmount Money (8)
> > > Declare @ShippingCost Money (8)
> > > Declare @RemainderAmount Money (8)
> > > Declare @ShipmentStatus Int (4)
> > > Declare @PaidInvoice Bit (1)
> > > Declare @PaidCommission Bit(1)
> > > Declare @ArrivalDate DateTime (8)
> > > Declare @IdTransport SmallInt (2)
> > > Declare @TransactionType Int (4)
> > > Declare @TimeStamp DateTime (8)
> > >
> > > Select @IdInvoice = (Select K_Factura From Inserted)
> > > Select @IdOrder = (Select K_Pedido From Inserted)
> > > Select @DateInvoice = (Select F_Factura From Inserted)
> > > Select @PaymentDay = (Select F_Vencimiento From Inserted)
> > > Select @TotalAmount = (Select Monto From Inserted)
> > > Select @ShippingCost = (Select Flete From Intserted)
> > > Select @RemainderAmount = (Select Saldo From Inserted)
> > > Select @ShipmentStatus = (Select Estatus_Envio From Inserted)
> > > Select @PaidInvoice = (Select Pagada From Inserted)
> > > Select @PaidCommission = (Select Comision_Pagada From Inserted)
> > > Select @ArrivalDate = (Select F_Llegada_Aduana From Inserted)
> > > Select @IdTransport = (Select K_Trasnporte From Inserted)
> > >
> > > Begin
> > > Insert InTo InvoiceExchange(IdInvoice, IdOrder, DateInvoice, PaymentDay,
> > > TotalAmount, ShippingCost, RemainderAmount, ShipmentStatus,
> > > PaidInvoice, PaidCommission, ArrivalDate, IdTransport)
> > >
> > > Values (@IdInvoice, @IdOrder, @DateInvoice, @PaymentDay, @TotalAmount,
> > > @ShippingCost, @RemainderAmount, @ShipmentStatus, @PaidInvoice,
> > > @PaidCommission, @ArrivalDate, @IdTransport)
> > > End