Post reply

Problem with trigger. Server: Msg 512, Level 16, State 1

  • October 5, 2005 at 4:48 pm

    #89569

    This trigger is supposed to copy data from deleted rows(deleted from via app I have no control over nor do I have control over the database structure) to another table. it works fine if there is only one record deleted. If there is more than one record being deleted I get the following error

    Server: Msg 512, Level 16, State 1

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.  The statement has been terminated.

    I suspect the problem is with settinng the variables. There will be times when there will be more than one accountno returned and they may be duplicates. Is it possible to accomodate this set of circumstances ?

     

    Thank you

     

    CREATE TRIGGER Supp_contact_Delete ON Contsupp

    FOR DELETE

    AS

    if((select rectype from deleted)='C' and (select accountno from deleted ) >' ')

    begin

    declare @recid varchar(15)

    declare @accountno varchar(20)

    declare @company varchar(40)

    declare @seccode varchar(20)

    insert into Contact1DEL

    (ACCOUNTNO, RECTYPE, CONTACT, TITLE, CONTSUPREF, DEAR, PHONE1, EXT1, FAX,  ADDRESS1,

    ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, MERGECODES,  LASTDATE, LASTTIME, RECID)

    select ACCOUNTNO, RECTYPE, CONTACT, TITLE, CONTSUPREF, DEAR, PHONE , EXT, FAX,  ADDRESS1,

    ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, MERGECODES, LASTDATE, LASTTIME, RECID

    from deleted

    select @recid = (select recid from deleted)

    select @accountno = (select accountno from deleted)

    select @company = (select company from contact1 where accountno = @accountno)

    select @seccode = (select key5 from contact1 where accountno = @accountno)

    update Contact1del

    set lastdate = getdate(), company = @company, key5 = @seccode where recid =  @recid

    end

  • October 5, 2005 at 5:02 pm

    #595310

    Remove all variables.

    update Contact1del

    set lastdate = getdate(),

         company = deleted.company,

         key5 = deleted.key5

    FROM deleted

    where Contact1del.recid =  deleted.recid

    Probaby you need and Contact1del.accountno = deleted.acccountno, depending on what is unique ID for this table.

     

    _____________
    Code for TallyGenerator

  • October 5, 2005 at 9:30 pm

    #595329

    Thanks for the assistance, Sergiy

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply