Please help, for I am a beginner with SQL Trigger.

• • Reply

  13 years ago

  by tkruvgt

  Van Thieu

  , Canada

  Joined 14 years ago

  Please help correcting this trigger, Thanks! Much appreciated.
  The problem i'm having is that, after a bulk insert into AirInvTemp, the trigger doesn't
  automatically copied the selected the filtered values into the new table AirInvoice, AirInvoiceItem
  
  

  USE

  [GUI]

  GO

  /****** Object: Trigger [dbo].[TR_AirInvTemp] Script Date: 04/10/2007 11:06:57 ******/

  SET

  ANSI_NULLS ON

  GO

  SET

  QUOTED_IDENTIFIER OFF

  GO

  CREATE

   TRIGGER [dbo].[TR_AirInvTemp] ON [dbo].[AirInvTemp]

  FOR

  INSERT

  AS

  DECLARE

  @DLI VARCHAR(3)

  DECLARE

  @VAT VARCHAR(1)

  DECLARE

  @TXNCODE VARCHAR(1)

  SELECT

  @TXNCode = TXNCode, @DLI = DLI034 FROM Inserted

  SELECT

  @VAT = ChargeCode.VAT FROM ChargeCode WHERE ChargeCode.Code = @DLI

  IF

  (@TXNCode = 'A' AND @VAT = 'Y')

  BEGIN

  INSERT INTO AirInvoice (

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId )

  SELECT

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId

  FROM Inserted

  INSERT INTO AirInvoiceItem (

  InvNo

  , ChargeCode, Charge, Term )

  SELECT

  InvNo

  , DLI034, DLI035, DLI036

  FROM Inserted

  INSERT INTO AirInvoiceHistory (

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId, DateOccured, ActionTaken )

  SELECT

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId,

  CONVERT(VARCHAR, GETDATE(), 112) AS DT, 'Add' AS ACT

  FROM Inserted

  INSERT INTO AirInvoiceItemHistory (

  InvNo

  , ChargeCode, Charge, Term, DateOccured, ActionTaken )

  SELECT

  InvNo

  , DLI034, DLI035, DLI036,

  CONVERT(VARCHAR,GETDATE(),112) AS DT, 'Add' AS ACT

  FROM Inserted

  END

  ELSE

  IF(@TXNCode = 'D' AND @VAT = 'Y')

  BEGIN

  INSERT INTO AirInvoiceHistory (

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId, DateOccured, ActionTaken )

  SELECT

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId,

  CONVERT(VARCHAR,GETDATE(),112) AS DT, 'Delete' AS ACT

  FROM Inserted

  INSERT INTO AirInvoiceItemHistory (

  InvNo

  , ChargeCode, Charge, Term, DateOccured, ActionTaken )

  SELECT

  InvNo

  , DLI034, DLI035, DLI036,

  CONVERT(VARCHAR,GETDATE(),112) AS DT, 'Delete' AS ACT

  FROM Inserted

  END

  ELSE

  IF(@TXNCode = 'C' AND @VAT = 'Y')

  BEGIN

  INSERT INTO AirInvoice(

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId )

  SELECT

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId

  FROM Inserted

  INSERT INTO AirInvoiceItem (

  InvNo

  , ChargeCode, Charge, Term )

  SELECT

  InvNo

  , DLI034, DLI035, DLI036

  FROM Inserted

  INSERT INTO AirInvoiceHistory (

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId, DateOccured, ActionTaken )

  SELECT

  Type

  , TXNCode, InvNo, InvDate, HawbNo, MawbNo,

  CustomerId

  , CustomerName, Attention, Addrs, City,

  State

  , Zip, Country, UniformId, FlyNo, FlyDate,

  Origin

  , Destination, Qty, Unit, GW, VolumeW,

  ChargeW

  , WType, CommodityCode, Remark, Currency,

  Collect

  , VATAmount, UserId,

  CONVERT(VARCHAR,GETDATE(),112) AS DT, 'Update' AS ACT

  FROM Inserted

  INSERT INTO AirInvoiceItemHistory (

  InvNo

  , ChargeCode, Charge, Term, DateOccured, ActionTaken )

  SELECT

  InvNo

  , DLI034, DLI035, DLI036,

  CONVERT(VARCHAR,GETDATE(),112) AS DT, 'Update' AS ACT

  FROM Inserted

  END

  Report abuse