quote:

---

Originally posted by Vack

In the trigger below I do not want it to run if the account is not equal 3051 or if freefield3 <> 'Rebate' either or would work I do not need them both. Problem I run into is there is a posting program that will insert records and it is bombing out because of the second update statement below. The posting program will be inserting records where freefield3 <> 'Rebate'

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT]
after INSERT
AS

begin
update gbkmut
set reknr = ' 3151', entryorigin = 'N', kredbep = 'K'
where freefield3 = 'Rebate'
end

begin
UPDATE gbkmut
SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate')
WHERE reknr = ' 1040'
end



This is the error I receive when the posting program runs.
Invalid advise flags

Source: Exact.EDL
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'bdr_hfl', table '001.dbo.gbkmut'; column does not allow nulls. UPDATE fails.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
SQL State: 23000
DB error: 515
EDL error: An error has occurred with the execution of the SQL statement 'INSERT INTO gbkmut (aantal,afldat,AmountCentral,artcode,bankacc,BankTransactionGUID,bdr_hfl,bdr_val,bdrkredbep,bdrkredbp2,betaalref,betcond,bkjrcode,bkstnr,bkstnr_sub,BlockItem,btw_bdr_3,btw_code,btw_grond,btw_grval,btw_nummer,btwper,CashRegisterAccount,CompanyCode,crdnr,CurrencyCode,CurrencyAliasAc,dagbknr,datum,dbk_verwnr,debnr,docdate,docnumber,DocAttachmentID,DocumentID,EntryGuid,entryorigin,exvalbdr,exvalcode,facode,faktuurnr,freefield1,freefield2,freefield3,freefield4,freefield5,PayrollCosts,koers,koers3,kredbep,kstdrcode,kstplcode,oms25,oorsprong,orderdebtor,PaymentMethod,PayrollSubtype,comp_code,periode,project,rapnr,Rate,ReconcileNumber,regel,regelcode,reknr,res_id,tegreknr,TransactionType,transtype,transsubtype,valcode,VATAmountCentral,VATBaseAmountCentral,vervdatfak,vervdatkrd,vervdtkrd2,verwerknrl,volgnr5,TransactionGuid,TransactionGuid2,warehouse,warehouse_location,wisselkrs,Shipment,Pricelist,Unitcode,Discount,Selcode,StockTrackingNumber,IntTransportMethod,IntPort,IntSystem,IntTransA,IntStatnr,IntStandardCode,IntTransshipment,IntTransB,IntArea,IntLandISO,IntLandDestOrig,IntDeliveryMethod,IntStatUnit,IntWeight,IntComplete,TaxCode2,TaxCode3,TaxCode4,TaxCode5,TaxBasis2,TaxBasis3,TaxBasis4,TaxBasis5,TaxAmount2,TaxAmount3,TaxAmount4,TaxAmount5,syscreated,syscreator,sysmodified,sysmodifier,sysguid) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'.

---

quote:

---

Originally posted by Vack

I only want the trigger to perform the update where the inserted records have freefield3 = 'Rebate'

---

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT] 
after INSERT
AS
If (SELECT freefield3 FROM Inserted) = 'Rebate'
begin
update gbkmut
set reknr = ' 3151', entryorigin = 'N', kredbep = 'K'
where freefield3 = 'Rebate'

UPDATE gbkmut
SET gbkmut.bdr_hfl = gbkmut.bdr_hfl - (SELECT SUM(inserted.bdr_hfl) FROM inserted WHERE inserted.freefield3 = 'Rebate')
WHERE reknr = ' 1040' 
end

quote:

---

Originally posted by Vack

Ok. The posting program works, but when I run my 2nd posting program that fires the triggers I get this error.

Server: Msg 512, Level 16, State 1, Procedure UPDATEEGBKMUTREBATERECORDS, Line 5
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.

---

quote:

---

Originally posted by Vack

Yes, I may be updating more than one row. My rule would be that any inserted records that have freefield3 = 'Rebate' then perform the trigger.

This is a summary of my current problem:
Posting Program 1: Works with If (SELECT freefield3 FROM Inserted) = 'Rebate'
Posting Program 2: Does not work with: If (SELECT freefield3 FROM Inserted) = 'Rebate'

Posting Program 1: Does not work without If (SELECT freefield3 FROM Inserted) = 'Rebate'
Posting Program 2: Does work without If (SELECT freefield3 FROM Inserted) = 'Rebate'

Hope this helps.

---

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT] 
after INSERT
AS
begin
DECLARE @Total FLOAT
SELECT @Total=ISNULL(SUM(inserted.bdr_hfl),0) FROM INSERTED 
WHERE inserted.freefield3 = 'Rebate'

update g
set reknr = ' 3151', entryorigin = 'N', kredbep = 'K'
FROM gbkmut g
INNER JOIN INSERTED i
ON i.PKCol=g.PKCol
where i.freefield3 = 'Rebate'

UPDATE g
SET gbkmut.bdr_hfl = g.bdr_hfl - @Total
INNER JOIN inserted i
ON i.PKCol=g.PKCol
WHERE i.freefield3 = 'Rebate'
AND  i.reknr = ' 1040' 
end

quote:

---

Originally posted by Vack

I've tried inserting that when I check the syntax I get Incorrect Syntax near the keyword inner. I have narrowed down to the last update statement. Nothing is jumping out at me:

CREATE TRIGGER [UPDATEEGBKMUTREBATERECORDS] ON [dbo].[GBKMUT]
after INSERT
AS
begin
DECLARE @Total FLOAT
SELECT @Total=ISNULL(SUM(inserted.bdr_hfl),0) FROM INSERTED
WHERE inserted.freefield3 = 'Rebate'

update g
set g.reknr = '3151', g.entryorigin = 'N', g.kredbep = 'K'
FROM gbkmut g
INNER JOIN INSERTED i
ON i.bkstnr_sub=g.bkstnr_sub
where i.freefield3 = 'Rebate'

UPDATE g
SET g.bdr_hfl = g.bdr_hfl-@Total
FROM gbkmut g
INNER JOIN inserted i
ON i.bkstnr_sub=g.bkstnr_sub
WHERE i.freefield3 = 'Rebate'
AND i.reknr = ' 1040'
end

---