Results 1 to 8 of 8

Thread: Trigger Error

  1. #1
    Join Date
    Mar 2017
    Posts
    4

    Unanswered: Trigger Error

    Hi everybody,

    you can find some codes following.I wrote a trigger for my db but ı get message like this "Implicit conversion from data type datetime to smallint is not allowed. Use the CONVERT function to run this query. can anyone help me ?

    CREATE
    trigger cariEkle on [MOT].[dbo].[AG_001_CLCARD]
    FOR
    DELETE
    AS
    BEGIN
    INSERT INTO [MOT].[dbo].[AG_002_CLCARD]
    ([ACTIVE]
    ,[CARDTYPE]
    ,[CODE]
    ,[DEFINITION_]
    ,[SPECODE]
    ,[CYPHCODE]
    ,[ADDR1]
    ,[ADDR2]
    ,[CITY]
    ,[COUNTRY]
    ,[POSTCODE]
    ,[TELNRS1]
    ,[TELNRS2]
    ,[FAXNR]
    ,[TAXNR]
    ,[TAXOFFICE]
    ,[INCHARGE]
    ,[DISCRATE]
    ,[EXTENREF]
    ,[PAYMENTREF]
    ,[EMAILADDR]
    ,[WEBADDR]
    ,[WARNMETHOD]
    ,[WARNEMAILADDR]
    ,[WARNFAXNR]
    ,[CLANGUAGE]
    ,[VATNR]
    ,[BLOCKED]
    ,[BANKBRANCHS1]
    ,[BANKBRANCHS2]
    ,[BANKBRANCHS3]
    ,[BANKBRANCHS4]
    ,[BANKBRANCHS5]
    ,[BANKBRANCHS6]
    ,[BANKBRANCHS7]
    ,[BANKACCOUNTS1]
    ,[BANKACCOUNTS2]
    ,[BANKACCOUNTS3]
    ,[BANKACCOUNTS4]
    ,[BANKACCOUNTS5]
    ,[BANKACCOUNTS6]
    ,[BANKACCOUNTS7]
    ,[DELIVERYMETHOD]
    ,[DELIVERYFIRM]
    ,[CCURRENCY]
    ,[TEXTINC]
    ,[SITEID]
    ,[RECSTATUS]
    ,[BANKIBANS5]
    ,[ORGLOGICREF]
    ,[EDINO]
    ,[TRADINGGRP]
    ,[CAPIBLOCK_CREATEDBY]
    ,[CAPIBLOCK_CREADEDDATE]
    ,[CAPIBLOCK_CREATEDHOUR]
    ,[CAPIBLOCK_CREATEDMIN]
    ,[CAPIBLOCK_CREATEDSEC]
    ,[CAPIBLOCK_MODIFIEDBY]
    ,[CAPIBLOCK_MODIFIEDDATE]
    ,[CAPIBLOCK_MODIFIEDHOUR]
    ,[CAPIBLOCK_MODIFIEDMIN]
    ,[CAPIBLOCK_MODIFIEDSEC]
    ,[PAYMENTPROC]
    ,[CRATEDIFFPROC]
    ,[WFSTATUS]
    ,[PPGROUPCODE]
    ,[PPGROUPREF]
    ,[TAXOFFCODE]
    ,[TOWNCODE]
    ,[TOWN]
    ,[DISTRICTCODE]
    ,[DISTRICT]
    ,[CITYCODE]
    ,[COUNTRYCODE]
    ,[ORDSENDMETHOD]
    ,[ORDSENDEMAILADDR]
    ,[ORDSENDFAXNR]
    ,[DSPSENDMETHOD]
    ,[DSPSENDEMAILADDR]
    ,[DSPSENDFAXNR]
    ,[INVSENDMETHOD]
    ,[INVSENDEMAILADDR]
    ,[INVSENDFAXNR]
    ,[SUBSCRIBERSTAT]
    ,[SUBSCRIBEREXT]
    ,[AUTOPAIDBANK]
    ,[PAYMENTTYPE]
    ,[LASTSENDREMLEV]
    ,[EXTACCESSFLAGS]
    ,[ORDSENDFORMAT]
    ,[DSPSENDFORMAT]
    ,[INVSENDFORMAT]
    ,[REMSENDFORMAT]
    ,[STORECREDITCARDNO]
    ,[CLORDFREQ]
    ,[ORDDAY]
    ,[LOGOID]
    ,[LIDCONFIRMED]
    ,[EXPREGNO]
    ,[EXPDOCNO]
    ,[EXPBUSTYPREF]
    ,[INVPRINTCNT]
    ,[PIECEORDINFLICT]
    ,[COLLECTINVOICING]
    ,[EBUSDATASENDTYPE]
    ,[INISTATUSFLAGS]
    ,[SLSORDERSTATUS]
    ,[SLSORDERPRICE]
    ,[LTRSENDMETHOD]
    ,[LTRSENDEMAILADDR]
    ,[LTRSENDFAXNR]
    ,[LTRSENDFORMAT]
    ,[IMAGEINC]
    ,[CELLPHONE]
    ,[SAMEITEMCODEUSE]
    ,[STATECODE]
    ,[STATENAME]
    ,[WFLOWCRDREF]
    ,[PARENTCLREF]
    ,[LOWLEVELCODES1]
    ,[LOWLEVELCODES2]
    ,[LOWLEVELCODES3]
    ,[LOWLEVELCODES4]
    ,[LOWLEVELCODES5]
    ,[LOWLEVELCODES6]
    ,[LOWLEVELCODES7]
    ,[LOWLEVELCODES8]
    ,[LOWLEVELCODES9]
    ,[LOWLEVELCODES10]
    ,[TELCODES1]
    ,[TELCODES2]
    ,[FAXCODE]
    ,[PURCHBRWS]
    ,[SALESBRWS]
    ,[IMPBRWS]
    ,[EXPBRWS]
    ,[FINBRWS]
    ,[ORGLOGOID]
    ,[ADDTOREFLIST]
    ,[TEXTREFTR]
    ,[TEXTREFEN]
    ,[ARPQUOTEINC]
    ,[CLCRM]
    ,[GRPFIRMNR]
    ,[CONSCODEREF]
    ,[SPECODE2]
    ,[SPECODE3]
    ,[SPECODE4]
    ,[SPECODE5]
    ,[OFFSENDMETHOD]
    ,[OFFSENDEMAILADDR]
    ,[OFFSENDFAXNR]
    ,[OFFSENDFORMAT]
    ,[EBANKNO]
    ,[LOANGRPCTRL]
    ,[BANKNAMES1]
    ,[BANKNAMES2]
    ,[BANKNAMES3]
    ,[BANKNAMES4]
    ,[BANKNAMES5]
    ,[BANKNAMES6]
    ,[BANKNAMES7]
    ,[LDXFIRMNR]
    ,[MAPID]
    ,[LONGITUDE]
    ,[LATITUTE]
    ,[CITYID]
    ,[TOWNID]
    ,[BANKIBANS1]
    ,[BANKIBANS2]
    ,[BANKIBANS3]
    ,[BANKIBANS4]
    ,[BANKIBANS6]
    ,[BANKIBANS7]
    ,[TCKNO]
    ,[ISPERSCOMP]
    ,[EXTSENDMETHOD]
    ,[EXTSENDEMAILADDR]
    ,[EXTSENDFAXNR]
    ,[EXTSENDFORMAT]
    ,[BANKBICS1]
    ,[BANKBICS2]
    ,[BANKBICS3]
    ,[BANKBICS4]
    ,[BANKBICS5]
    ,[BANKBICS6]
    ,[BANKBICS7]
    ,[CASHREF]
    ,[USEDINPERIODS]
    ,[INCHARGE2]
    ,[INCHARGE3]
    ,[EMAILADDR2]
    ,[EMAILADDR3]
    ,[RSKLIMCR]
    ,[RSKDUEDATECR]
    ,[RSKAGINGCR]
    ,[RSKAGINGDAY]
    ,[ACCEPTEINV]
    ,[EINVOICEID]
    ,[PROFILEID]
    ,[BANKBCURRENCY1]
    ,[BANKBCURRENCY2]
    ,[BANKBCURRENCY3]
    ,[BANKBCURRENCY4]
    ,[BANKBCURRENCY5]
    ,[BANKBCURRENCY6]
    ,[BANKBCURRENCY7]
    ,[PURCORDERSTATUS]
    ,[PURCORDERPRICE]
    ,[ISFOREIGN]
    ,[DBSBANKCURRENCY4]
    ,[DBSBANKCURRENCY3]
    ,[DBSBANKCURRENCY2]
    ,[DBSBANKCURRENCY7]
    ,[DBSBANKCURRENCY6]
    ,[DBSBANKCURRENCY5]
    ,[DBSBANKCURRENCY1]
    ,[DBSRISKCNTRL4]
    ,[DBSRISKCNTRL3]
    ,[DBSRISKCNTRL2]
    ,[DBSRISKCNTRL7]
    ,[DBSRISKCNTRL6]
    ,[DBSRISKCNTRL5]
    ,[BANKCORRPACC1]
    ,[BANKVOEN4]
    ,[BANKVOEN3]
    ,[BANKVOEN2]
    ,[BANKVOEN7]
    ,[BANKVOEN6]
    ,[BANKVOEN5]
    ,[BANKVOEN1]
    ,[BANKCORRPACC4]
    ,[BANKCORRPACC3]
    ,[BANKCORRPACC2]
    ,[BANKCORRPACC7]
    ,[BANKCORRPACC6]
    ,[BANKCORRPACC5]
    ,[DBSRISKCNTRL1]
    ,[DBSLIMIT4]
    ,[DBSLIMIT3]
    ,[DBSLIMIT2]
    ,[DBSLIMIT7]
    ,[DBSLIMIT6]
    ,[DBSLIMIT5]
    ,[DBSLIMIT1]
    ,[SHIPBEGTIME3]
    ,[SHIPBEGTIME2]
    ,[SHIPBEGTIME1]
    ,[SHIPENDTIME3]
    ,[SHIPENDTIME2]
    ,[SHIPENDTIME1]
    ,[DBSTOTAL1]
    ,[DBSBANKNO4]
    ,[DBSBANKNO3]
    ,[DBSBANKNO2]
    ,[DBSBANKNO7]
    ,[DBSBANKNO6]
    ,[DBSBANKNO5]
    ,[DBSBANKNO1]
    ,[DBSTOTAL4]
    ,[DBSTOTAL3]
    ,[DBSTOTAL2]
    ,[DBSTOTAL7]
    ,[DBSTOTAL6]
    ,[DBSTOTAL5])
    SELECT
    [ACTIVE]
    ,[CARDTYPE]
    ,[CODE]
    ,[DEFINITION_]
    ,[SPECODE]
    ,[CYPHCODE]
    ,[ADDR1]
    ,[ADDR2]
    ,[CITY]
    ,[COUNTRY]
    ,[POSTCODE]
    ,[TELNRS1]
    ,[TELNRS2]
    ,[FAXNR]
    ,[TAXNR]
    ,[TAXOFFICE]
    ,[INCHARGE]
    ,[DISCRATE]
    ,[EXTENREF]
    ,[PAYMENTREF]
    ,[EMAILADDR]
    ,[WEBADDR]
    ,[WARNMETHOD]
    ,[WARNEMAILADDR]
    ,[WARNFAXNR]
    ,[CLANGUAGE]
    ,[VATNR]
    ,[BLOCKED]
    ,[BANKBRANCHS1]
    ,[BANKBRANCHS2]
    ,[BANKBRANCHS3]
    ,[BANKBRANCHS4]
    ,[BANKBRANCHS5]
    ,[BANKBRANCHS6]
    ,[BANKBRANCHS7]
    ,[BANKACCOUNTS1]
    ,[BANKACCOUNTS2]
    ,[BANKACCOUNTS3]
    ,[BANKACCOUNTS4]
    ,[BANKACCOUNTS5]
    ,[BANKACCOUNTS6]
    ,[BANKACCOUNTS7]
    ,[DELIVERYMETHOD]
    ,[DELIVERYFIRM]
    ,[CCURRENCY]
    ,[TEXTINC]
    ,[SITEID]
    ,[RECSTATUS]
    ,[ORGLOGICREF]
    ,[EDINO]
    ,[TRADINGGRP]
    ,[CAPIBLOCK_CREATEDBY]
    ,[CAPIBLOCK_CREADEDDATE]
    ,[CAPIBLOCK_CREATEDHOUR]
    ,[CAPIBLOCK_CREATEDMIN]
    ,[CAPIBLOCK_CREATEDSEC]
    ,[CAPIBLOCK_MODIFIEDBY]
    ,[CAPIBLOCK_MODIFIEDDATE]
    ,[CAPIBLOCK_MODIFIEDHOUR]
    ,[CAPIBLOCK_MODIFIEDMIN]
    ,[CAPIBLOCK_MODIFIEDSEC]
    ,[PAYMENTPROC]
    ,[CRATEDIFFPROC]
    ,[WFSTATUS]
    ,[PPGROUPCODE]
    ,[PPGROUPREF]
    ,[TAXOFFCODE]
    ,[TOWNCODE]
    ,[TOWN]
    ,[DISTRICTCODE]
    ,[DISTRICT]
    ,[CITYCODE]
    ,[COUNTRYCODE]
    ,[ORDSENDMETHOD]
    ,[ORDSENDEMAILADDR]
    ,[ORDSENDFAXNR]
    ,[DSPSENDMETHOD]
    ,[DSPSENDEMAILADDR]
    ,[DSPSENDFAXNR]
    ,[INVSENDMETHOD]
    ,[INVSENDEMAILADDR]
    ,[INVSENDFAXNR]
    ,[SUBSCRIBERSTAT]
    ,[SUBSCRIBEREXT]
    ,[AUTOPAIDBANK]
    ,[PAYMENTTYPE]
    ,[LASTSENDREMLEV]
    ,[EXTACCESSFLAGS]
    ,[ORDSENDFORMAT]
    ,[DSPSENDFORMAT]
    ,[INVSENDFORMAT]
    ,[REMSENDFORMAT]
    ,[STORECREDITCARDNO]
    ,[CLORDFREQ]
    ,[ORDDAY]
    ,[LOGOID]
    ,[LIDCONFIRMED]
    ,[EXPREGNO]
    ,[EXPDOCNO]
    ,[EXPBUSTYPREF]
    ,[INVPRINTCNT]
    ,[PIECEORDINFLICT]
    ,[COLLECTINVOICING]
    ,[EBUSDATASENDTYPE]
    ,[INISTATUSFLAGS]
    ,[SLSORDERSTATUS]
    ,[SLSORDERPRICE]
    ,[LTRSENDMETHOD]
    ,[LTRSENDEMAILADDR]
    ,[LTRSENDFAXNR]
    ,[LTRSENDFORMAT]
    ,[IMAGEINC]
    ,[CELLPHONE]
    ,[SAMEITEMCODEUSE]
    ,[STATECODE]
    ,[STATENAME]
    ,[WFLOWCRDREF]
    ,[PARENTCLREF]
    ,[LOWLEVELCODES1]
    ,[LOWLEVELCODES2]
    ,[LOWLEVELCODES3]
    ,[LOWLEVELCODES4]
    ,[LOWLEVELCODES5]
    ,[LOWLEVELCODES6]
    ,[LOWLEVELCODES7]
    ,[LOWLEVELCODES8]
    ,[LOWLEVELCODES9]
    ,[LOWLEVELCODES10]
    ,[TELCODES1]
    ,[TELCODES2]
    ,[FAXCODE]
    ,[PURCHBRWS]
    ,[SALESBRWS]
    ,[IMPBRWS]
    ,[EXPBRWS]
    ,[FINBRWS]
    ,[ORGLOGOID]
    ,[ADDTOREFLIST]
    ,[TEXTREFTR]
    ,[TEXTREFEN]
    ,[ARPQUOTEINC]
    ,[CLCRM]
    ,[GRPFIRMNR]
    ,[CONSCODEREF]
    ,[SPECODE2]
    ,[SPECODE3]
    ,[SPECODE4]
    ,[SPECODE5]
    ,[OFFSENDMETHOD]
    ,[OFFSENDEMAILADDR]
    ,[OFFSENDFAXNR]
    ,[OFFSENDFORMAT]
    ,[EBANKNO]
    ,[LOANGRPCTRL]
    ,[BANKNAMES1]
    ,[BANKNAMES2]
    ,[BANKNAMES3]
    ,[BANKNAMES4]
    ,[BANKNAMES5]
    ,[BANKNAMES6]
    ,[BANKNAMES7]
    ,[LDXFIRMNR]
    ,[MAPID]
    ,[LONGITUDE]
    ,[LATITUTE]
    ,[CITYID]
    ,[TOWNID]
    ,[BANKIBANS1]
    ,[BANKIBANS2]
    ,[BANKIBANS3]
    ,[BANKIBANS4]
    ,[BANKIBANS5]
    ,[BANKIBANS6]
    ,[BANKIBANS7]
    ,[TCKNO]
    ,[ISPERSCOMP]
    ,[EXTSENDMETHOD]
    ,[EXTSENDEMAILADDR]
    ,[EXTSENDFAXNR]
    ,[EXTSENDFORMAT]
    ,[BANKBICS1]
    ,[BANKBICS2]
    ,[BANKBICS3]
    ,[BANKBICS4]
    ,[BANKBICS5]
    ,[BANKBICS6]
    ,[BANKBICS7]
    ,[CASHREF]
    ,[USEDINPERIODS]
    ,[INCHARGE2]
    ,[INCHARGE3]
    ,[EMAILADDR2]
    ,[EMAILADDR3]
    ,[RSKLIMCR]
    ,[RSKDUEDATECR]
    ,[RSKAGINGCR]
    ,[RSKAGINGDAY]
    ,[ACCEPTEINV]
    ,[EINVOICEID]
    ,[PROFILEID]
    ,[BANKBCURRENCY1]
    ,[BANKBCURRENCY2]
    ,[BANKBCURRENCY3]
    ,[BANKBCURRENCY4]
    ,[BANKBCURRENCY5]
    ,[BANKBCURRENCY6]
    ,[BANKBCURRENCY7]
    ,[PURCORDERSTATUS]
    ,[PURCORDERPRICE]
    ,[ISFOREIGN]
    ,[DBSBANKCURRENCY4]
    ,[DBSBANKCURRENCY3]
    ,[DBSBANKCURRENCY2]
    ,[DBSBANKCURRENCY7]
    ,[DBSBANKCURRENCY6]
    ,[DBSBANKCURRENCY5]
    ,[DBSBANKCURRENCY1]
    ,[DBSRISKCNTRL4]
    ,[DBSRISKCNTRL3]
    ,[DBSRISKCNTRL2]
    ,[DBSRISKCNTRL7]
    ,[DBSRISKCNTRL6]
    ,[DBSRISKCNTRL5]
    ,[BANKCORRPACC1]
    ,[BANKVOEN4]
    ,[BANKVOEN3]
    ,[BANKVOEN2]
    ,[BANKVOEN7]
    ,[BANKVOEN6]
    ,[BANKVOEN5]
    ,[BANKVOEN1]
    ,[BANKCORRPACC4]
    ,[BANKCORRPACC3]
    ,[BANKCORRPACC2]
    ,[BANKCORRPACC7]
    ,[BANKCORRPACC6]
    ,[BANKCORRPACC5]
    ,[DBSRISKCNTRL1]
    ,[DBSLIMIT4]
    ,[DBSLIMIT3]
    ,[DBSLIMIT2]
    ,[DBSLIMIT7]
    ,[DBSLIMIT6]
    ,[DBSLIMIT5]
    ,[DBSLIMIT1]
    ,[SHIPBEGTIME3]
    ,[SHIPBEGTIME2]
    ,[SHIPBEGTIME1]
    ,[SHIPENDTIME3]
    ,[SHIPENDTIME2]
    ,[SHIPENDTIME1]
    ,[DBSTOTAL1]
    ,[DBSBANKNO4]
    ,[DBSBANKNO3]
    ,[DBSBANKNO2]
    ,[DBSBANKNO7]
    ,[DBSBANKNO6]
    ,[DBSBANKNO5]
    ,[DBSBANKNO1]
    ,[DBSTOTAL4]
    ,[DBSTOTAL3]
    ,[DBSTOTAL2]
    ,[DBSTOTAL7]
    ,[DBSTOTAL6]
    ,[DBSTOTAL5]
    FROM
    inserted
    WHERE
    CODE =(SELECT CODE FROM inserted)
    END

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    Most likely you have a mismatch between the columns you are inserting, and the columns you are selecting. Take both lists, line them up next to each other in Excel, and you should be able to spot the problem fairly quickly.

  3. #3
    Join Date
    Mar 2017
    Posts
    4
    Quote Originally Posted by MCrowley View Post
    Most likely you have a mismatch between the columns you are inserting, and the columns you are selecting. Take both lists, line them up next to each other in Excel, and you should be able to spot the problem fairly quickly.
    Dear MCrowley,

    Your solution way is correct definitely,I did it and scripts run.There was a mismatch parameter.

    Thank you so much.

    best regards.

  4. #4
    Join Date
    Mar 2017
    Posts
    4
    Dear MCrowley,

    I want to convert these codes to update trigger but I cant success.Can you help me please?

    CREATE
    TRIGGER cariekle on [LKSDB].[dbo].[LG_117_CLCARD]
    FOR
    UPDATE
    AS
    BEGIN
    INSERT
    INTO [LKSDB].[dbo].[LG_100_CLCARD]
    ([ACTIVE]
    ,[CARDTYPE]
    ,[CODE]
    ,[DEFINITION_]
    ,[SPECODE]
    ,[CYPHCODE])
    SELECT
    [ACTIVE]
    ,[CARDTYPE]
    ,[CODE]
    ,[DEFINITION_]
    ,[SPECODE]
    ,[CYPHCODE]
    FROM
    inserted
    WHERE
    CODE =(SELECT CODE FROM inserted)
    END

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    I can see as that would fail with a "Subquery returned more than one value" error. What is the error you are getting?

  6. #6
    Join Date
    Mar 2017
    Posts
    4
    Quote Originally Posted by MCrowley View Post
    I can see as that would fail with a "Subquery returned more than one value" error. What is the error you are getting?
    Hi MCrowley,

    Actually,the codes run but after that when I add an customer account to db, db has a message to screen " db server is not ready right now,please try again later".Note : I didnt write all codes because ıt is too long.

  7. #7
    Join Date
    Dec 2016
    Posts
    8
    Trigger Error, you should follow this article to resolve your Trigger error https://www.janbasktraining.com/blog...er-sql-server/

  8. #8
    Join Date
    Sep 2017
    Posts
    4

    Mismatch

    Most likely you have a mismatch between the columns you are inserting, and the columns you are selecting. Take both lists, line them up next to each other in Excel, and you should be able to spot the problem fairly quickly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •