jdr0606
asked on
Error when creating a trigger
I'm trying to create a simple trigger but I keep getting an error on creation that says
Msg 120, Level 15, State 1, Procedure tr_trnSOP10200_Delete, Line 13
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
The list seems to be the same
What am I missing?
The trigger is below
USE [compdb]
GO
/****** Object: Trigger [dbo].[tr_trnSOP10200_Dele te] Script Date: 03/21/2012 22:45:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[tr_trnSOP10200_Dele te]
ON [dbo].[SOP10200] FOR DELETE
AS
BEGIN
/* Capture delete detail on SOP10200 */
INSERT INTO DYNAMICS_EXT.dbo.trnSOP102 00_Detail
(
login_Name
,user_name
,spid
,hostname
,trnAction
,get_date
, SOPTYPE
, SOPNUMBE
, LNITMSEQ
, CMPNTSEQ
, ITEMNMBR
, ITEMDESC
, NONINVEN
, DROPSHIP
, UOFM
, LOCNCODE
, UNITCOST
, ORUNTCST
, UNITPRCE
, ORUNTPRC
, XTNDPRCE
, OXTNDPRC
, REMPRICE
, OREPRICE
, EXTDCOST
, OREXTCST
, MRKDNAMT
, ORMRKDAM
, MRKDNPCT
, MRKDNTYP
, INVINDX
, CSLSINDX
, SLSINDX
, MKDNINDX
, RTNSINDX
, INUSINDX
, INSRINDX
, DMGDINDX
, ITMTSHID
, IVITMTXB
, BKTSLSAM
, ORBKTSLS
, TAXAMNT
, ORTAXAMT
, TXBTXAMT
, OTAXTAMT
, BSIVCTTL
, TRDISAMT
, ORTDISAM
, DISCSALE
, ORDAVSLS
, QUANTITY
, ATYALLOC
, QTYINSVC
, QTYINUSE
, QTYDMGED
, QTYRTRND
, QTYONHND
, QTYCANCE
, QTYCANOT
, QTYONPO
, QTYORDER
, QTYPRBAC
, QTYPRBOO
, QTYPRINV
, QTYPRORD
, QTYPRVRECVD
, QTYRECVD
, QTYREMAI
, QTYREMBO
, QTYTBAOR
, QTYTOINV
, QTYTORDR
, QTYFULFI
, QTYSLCTD
, QTYBSUOM
, EXTQTYAL
, EXTQTYSEL
, ReqShipDate
, FUFILDAT
, ACTLSHIP
, SHIPMTHD
, SALSTERR
, SLPRSNID
, PRCLEVEL
, COMMNTID
, BRKFLD1
, BRKFLD2
, BRKFLD3
, CURRNIDX
, TRXSORCE
, SOPLNERR
, ORGSEQNM
, ITEMCODE
, PURCHSTAT
, DECPLQTY
, DECPLCUR
, ODECPLCU
, QTYTOSHP
, XFRSHDOC
, EXCEPTIONALDEMAND
, TAXSCHID
, TXSCHSRC
, PRSTADCD
, ShipToName
, CNTCPRSN
, ADDRESS1
, ADDRESS2
, ADDRESS3
, CITY
, [STATE]
, ZIPCODE
, CCode
, COUNTRY
, PHONE1
, PHONE2
, PHONE3
, FAXNUMBR
, Flags
, BackoutTradeDisc
, OrigBackoutTradeDisc
, GPSFOINTEGRATIONID
, INTEGRATIONSOURCE
, INTEGRATIONID
, CONTNBR
, CONTLNSEQNBR
, CONTSTARTDTE
, CONTENDDTE
, CONTITEMNBR
, CONTSERIALNBR
, BULKPICKPRNT
, INDPICKPRNT
, ISLINEINTRA
, SOFULFILLMENTBIN
, MULTIPLEBINS
)
SELECT
system_user)login_Name
,(user)user_name
,(@@spid)spid
,(host_name())hostname
,('Delete')trnAction
,(getdate())get_date
, SOPTYPE
, SOPNUMBE
, LNITMSEQ
, CMPNTSEQ
, ITEMNMBR
, ITEMDESC
, NONINVEN
, DROPSHIP
, UOFM
, LOCNCODE
, UNITCOST
, ORUNTCST
, UNITPRCE
, ORUNTPRC
, XTNDPRCE
, OXTNDPRC
, REMPRICE
, OREPRICE
, EXTDCOST
, OREXTCST
, MRKDNAMT
, ORMRKDAM
, MRKDNPCT
, MRKDNTYP
, INVINDX
, CSLSINDX
, SLSINDX
, MKDNINDX
, RTNSINDX
, INUSINDX
, INSRINDX
, DMGDINDX
, ITMTSHID
, IVITMTXB
, BKTSLSAM
, ORBKTSLS
, TAXAMNT
, ORTAXAMT
, TXBTXAMT
, OTAXTAMT
, BSIVCTTL
, TRDISAMT
, ORTDISAM
, DISCSALE
, ORDAVSLS
, QUANTITY
, ATYALLOC
, QTYINSVC
, QTYINUSE
, QTYDMGED
, QTYRTRND
, QTYONHND
, QTYCANCE
, QTYCANOT
, QTYONPO
, QTYORDER
, QTYPRBAC
, QTYPRBOO
, QTYPRINV
, QTYPRORD
, QTYPRVRECVD
, QTYRECVD
, QTYREMAI
, QTYREMBO
, QTYTBAOR
, QTYTOINV
, QTYTORDR
, QTYFULFI
, QTYSLCTD
, QTYBSUOM
, EXTQTYAL
, EXTQTYSEL
, ReqShipDate
, FUFILDAT
, ACTLSHIP
, SHIPMTHD
, SALSTERR
, SLPRSNID
, PRCLEVEL
, COMMNTID
, BRKFLD1
, BRKFLD2
, BRKFLD3
, CURRNIDX
, TRXSORCE
, SOPLNERR
, ORGSEQNM
, ITEMCODE
, PURCHSTAT
, DECPLQTY
, DECPLCUR
, ODECPLCU
, QTYTOSHP
, XFRSHDOC
, EXCEPTIONALDEMAND
, TAXSCHID
, TXSCHSRC
, PRSTADCD
, ShipToName
, CNTCPRSN
, ADDRESS1
, ADDRESS2
, ADDRESS3
, CITY
, [STATE]
, ZIPCODE
, CCode
, COUNTRY
, PHONE1
, PHONE2
, PHONE3
, FAXNUMBR
, Flags
, BackoutTradeDisc
, OrigBackoutTradeDisc
, GPSFOINTEGRATIONID
, INTEGRATIONSOURCE
, INTEGRATIONID
, CONTNBR
, CONTLNSEQNBR
, CONTSTARTDTE
, CONTENDDTE
, CONTITEMNBR
, CONTSERIALNBR
, BULKPICKPRNT
, INDPICKPRNT
, ISLINEINTRA
, SOFULFILLMENTBIN
, MULTIPLEBINS
FROM DELETED
END
Msg 120, Level 15, State 1, Procedure tr_trnSOP10200_Delete, Line 13
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
The list seems to be the same
What am I missing?
The trigger is below
USE [compdb]
GO
/****** Object: Trigger [dbo].[tr_trnSOP10200_Dele
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create TRIGGER [dbo].[tr_trnSOP10200_Dele
ON [dbo].[SOP10200] FOR DELETE
AS
BEGIN
/* Capture delete detail on SOP10200 */
INSERT INTO DYNAMICS_EXT.dbo.trnSOP102
(
login_Name
,user_name
,spid
,hostname
,trnAction
,get_date
, SOPTYPE
, SOPNUMBE
, LNITMSEQ
, CMPNTSEQ
, ITEMNMBR
, ITEMDESC
, NONINVEN
, DROPSHIP
, UOFM
, LOCNCODE
, UNITCOST
, ORUNTCST
, UNITPRCE
, ORUNTPRC
, XTNDPRCE
, OXTNDPRC
, REMPRICE
, OREPRICE
, EXTDCOST
, OREXTCST
, MRKDNAMT
, ORMRKDAM
, MRKDNPCT
, MRKDNTYP
, INVINDX
, CSLSINDX
, SLSINDX
, MKDNINDX
, RTNSINDX
, INUSINDX
, INSRINDX
, DMGDINDX
, ITMTSHID
, IVITMTXB
, BKTSLSAM
, ORBKTSLS
, TAXAMNT
, ORTAXAMT
, TXBTXAMT
, OTAXTAMT
, BSIVCTTL
, TRDISAMT
, ORTDISAM
, DISCSALE
, ORDAVSLS
, QUANTITY
, ATYALLOC
, QTYINSVC
, QTYINUSE
, QTYDMGED
, QTYRTRND
, QTYONHND
, QTYCANCE
, QTYCANOT
, QTYONPO
, QTYORDER
, QTYPRBAC
, QTYPRBOO
, QTYPRINV
, QTYPRORD
, QTYPRVRECVD
, QTYRECVD
, QTYREMAI
, QTYREMBO
, QTYTBAOR
, QTYTOINV
, QTYTORDR
, QTYFULFI
, QTYSLCTD
, QTYBSUOM
, EXTQTYAL
, EXTQTYSEL
, ReqShipDate
, FUFILDAT
, ACTLSHIP
, SHIPMTHD
, SALSTERR
, SLPRSNID
, PRCLEVEL
, COMMNTID
, BRKFLD1
, BRKFLD2
, BRKFLD3
, CURRNIDX
, TRXSORCE
, SOPLNERR
, ORGSEQNM
, ITEMCODE
, PURCHSTAT
, DECPLQTY
, DECPLCUR
, ODECPLCU
, QTYTOSHP
, XFRSHDOC
, EXCEPTIONALDEMAND
, TAXSCHID
, TXSCHSRC
, PRSTADCD
, ShipToName
, CNTCPRSN
, ADDRESS1
, ADDRESS2
, ADDRESS3
, CITY
, [STATE]
, ZIPCODE
, CCode
, COUNTRY
, PHONE1
, PHONE2
, PHONE3
, FAXNUMBR
, Flags
, BackoutTradeDisc
, OrigBackoutTradeDisc
, GPSFOINTEGRATIONID
, INTEGRATIONSOURCE
, INTEGRATIONID
, CONTNBR
, CONTLNSEQNBR
, CONTSTARTDTE
, CONTENDDTE
, CONTITEMNBR
, CONTSERIALNBR
, BULKPICKPRNT
, INDPICKPRNT
, ISLINEINTRA
, SOFULFILLMENTBIN
, MULTIPLEBINS
)
SELECT
system_user)login_Name
,(user)user_name
,(@@spid)spid
,(host_name())hostname
,('Delete')trnAction
,(getdate())get_date
, SOPTYPE
, SOPNUMBE
, LNITMSEQ
, CMPNTSEQ
, ITEMNMBR
, ITEMDESC
, NONINVEN
, DROPSHIP
, UOFM
, LOCNCODE
, UNITCOST
, ORUNTCST
, UNITPRCE
, ORUNTPRC
, XTNDPRCE
, OXTNDPRC
, REMPRICE
, OREPRICE
, EXTDCOST
, OREXTCST
, MRKDNAMT
, ORMRKDAM
, MRKDNPCT
, MRKDNTYP
, INVINDX
, CSLSINDX
, SLSINDX
, MKDNINDX
, RTNSINDX
, INUSINDX
, INSRINDX
, DMGDINDX
, ITMTSHID
, IVITMTXB
, BKTSLSAM
, ORBKTSLS
, TAXAMNT
, ORTAXAMT
, TXBTXAMT
, OTAXTAMT
, BSIVCTTL
, TRDISAMT
, ORTDISAM
, DISCSALE
, ORDAVSLS
, QUANTITY
, ATYALLOC
, QTYINSVC
, QTYINUSE
, QTYDMGED
, QTYRTRND
, QTYONHND
, QTYCANCE
, QTYCANOT
, QTYONPO
, QTYORDER
, QTYPRBAC
, QTYPRBOO
, QTYPRINV
, QTYPRORD
, QTYPRVRECVD
, QTYRECVD
, QTYREMAI
, QTYREMBO
, QTYTBAOR
, QTYTOINV
, QTYTORDR
, QTYFULFI
, QTYSLCTD
, QTYBSUOM
, EXTQTYAL
, EXTQTYSEL
, ReqShipDate
, FUFILDAT
, ACTLSHIP
, SHIPMTHD
, SALSTERR
, SLPRSNID
, PRCLEVEL
, COMMNTID
, BRKFLD1
, BRKFLD2
, BRKFLD3
, CURRNIDX
, TRXSORCE
, SOPLNERR
, ORGSEQNM
, ITEMCODE
, PURCHSTAT
, DECPLQTY
, DECPLCUR
, ODECPLCU
, QTYTOSHP
, XFRSHDOC
, EXCEPTIONALDEMAND
, TAXSCHID
, TXSCHSRC
, PRSTADCD
, ShipToName
, CNTCPRSN
, ADDRESS1
, ADDRESS2
, ADDRESS3
, CITY
, [STATE]
, ZIPCODE
, CCode
, COUNTRY
, PHONE1
, PHONE2
, PHONE3
, FAXNUMBR
, Flags
, BackoutTradeDisc
, OrigBackoutTradeDisc
, GPSFOINTEGRATIONID
, INTEGRATIONSOURCE
, INTEGRATIONID
, CONTNBR
, CONTLNSEQNBR
, CONTSTARTDTE
, CONTENDDTE
, CONTITEMNBR
, CONTSERIALNBR
, BULKPICKPRNT
, INDPICKPRNT
, ISLINEINTRA
, SOFULFILLMENTBIN
, MULTIPLEBINS
FROM DELETED
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.