?
Solved

Error when creating a trigger

Posted on 2012-03-21
1
Medium Priority
?
282 Views
Last Modified: 2012-03-21
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_Delete]    Script Date: 03/21/2012 22:45:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




Create TRIGGER [dbo].[tr_trnSOP10200_Delete]
ON [dbo].[SOP10200] FOR DELETE
AS
BEGIN

/* Capture delete detail on SOP10200 */

      
INSERT INTO DYNAMICS_EXT.dbo.trnSOP10200_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
0
Comment
Question by:jdr0606
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 37750996
try changing

SELECT
      system_user)login_Name
    ,(user)user_name
    ,(@@spid)spid
    ,(host_name())hostname
    ,('Delete')trnAction
      ,(getdate())get_date      


to


SELECT
      system_user login_Name
    ,[user] user_name
    ,@@spid spid
    , host_name() hostname
    ,'Delete' trnAction
      ,getdate() get_date      
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question