Solved

Error when creating a trigger

Posted on 2012-03-21
1
275 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
1 Comment
 
LVL 39

Accepted Solution

by:
appari earned 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

789 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