Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Enhancement Requests and Suggestions » View Thread |
Messages 1 to 3 of 3 total |
OLDROW NEWROW |
Tue, Feb 12 2019 10:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Can these be set to NULL if referenced in an ALL trigger and its not the right sort please.
I was getting ElevateDB Error #1011 An error occurred with the row OLDROW (The row value cannot be referenced in an INSERT operation) This was at the start of the trigger DECLARE OldBank BOOLEAN DEFAULT FALSE; DECLARE NewBank BOOLEAN DEFAULT FALSE; DECLARE Check VARCHAR DEFAULT 'SELECT IF(COUNT(*) > 0,TRUE,FALSE) INTO ? FROM Banks WHERE _Type IN (''B'',''P'') AND _ID = '; DECLARE DelTran VARCHAR DEFAULT 'UPDATE Banks SET _ReconciledBalance = (_ReconcileBalance - OLDROW._Income) + OLDROW._Expenditure WHERE _ID = '+CAST(OLDROW._fkBanks,VARCHAR(10)); DECLARE IncTran VARCHAR DEFAULT 'UPDATE Banks SET _ReconciledBalance = (_ReconcileBalance - NEWROW._Expenditure) + NEWROW._Income WHERE _ID = '+CAST(NEWROW._fkBanks,VARCHAR(10)); I then had a load of IF..ELSEIF which would stop me using the wrong statement - took me ages to realise it was the declarations Roy Lambert |
Tue, Feb 12 2019 2:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Can these be set to NULL if referenced in an ALL trigger and its not the right sort please. >> Not to answer a question with a question, but why are you using UPDATE statements in DEFAULT clauses ? That SQL looks a bit messed up, is why I'm asking... Tim Young Elevate Software www.elevatesoft.com |
Wed, Feb 13 2019 3:09 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>Not to answer a question with a question, but why are you using UPDATE statements in DEFAULT clauses ? That SQL looks a bit messed up, is why I'm asking... I wasn't I was simply setting a text string. Basically depending on the operation I'd be doing each via EXECUTE IMMEDIATE in a later place. What I ultimately ended up with was CREATE TRIGGER "BankUpdate" AFTER ALL ON "Transactions" BEGIN /* INSERT If NEWROW._Reconciled then increment newbank - oldbank should not exist DELETE if OLDROW._Reconciled then decrement oldbank - newbank should not exist - should not really happen but be blocked in the UI UPDATE if OLDROW._Reconciled then decrement oldbank if NEWROW._Reconciled the increment newbank */ DECLARE OldBank BOOLEAN DEFAULT FALSE; DECLARE NewBank BOOLEAN DEFAULT FALSE; DECLARE Check VARCHAR DEFAULT 'SELECT IF(COUNT(*) > 0,TRUE,FALSE) INTO ? FROM Banks WHERE _Type IN (''B'',''P'') AND _ID = '; DECLARE UpdateTran VARCHAR DEFAULT 'UPDATE Banks SET _ReconciledBalance = (_ReconciledBalance - ?) + ? WHERE _ID = ?'; IF OPERATION() = 'Update' THEN IF OLDROW._Reconciled THEN EXECUTE IMMEDIATE Check + CAST(OLDROW._fkBanks,VARCHAR(10)) USING OldBank; IF OldBank THEN EXECUTE IMMEDIATE UpdateTran USING COALESCE(OLDROW._Income,0),COALESCE(OLDROW._Expenditure,0),+ OLDROW._fkBanks ; END IF; END IF; IF NEWROW._Reconciled THEN EXECUTE IMMEDIATE Check + CAST(NEWROW._fkBanks,VARCHAR(10)) USING NewBank; IF NewBank THEN EXECUTE IMMEDIATE UpdateTran USING COALESCE(NEWROW._Expenditure,0),COALESCE(NEWROW._Income,0),+ NEWROW._fkBanks ; END IF; END IF; ELSEIF OPERATION() = 'Insert' THEN IF NEWROW._Reconciled THEN EXECUTE IMMEDIATE Check + CAST(NEWROW._fkBanks,VARCHAR(10)) USING NewBank; IF NewBank THEN EXECUTE IMMEDIATE UpdateTran USING COALESCE(NEWROW._Expenditure,0),COALESCE(NEWROW._Income,0),+ NEWROW._fkBanks ; END IF; END IF; ELSEIF OPERATION() = 'Delete' THEN IF OLDROW._Reconciled THEN EXECUTE IMMEDIATE Check + CAST(OLDROW._fkBanks,VARCHAR(10)) USING OldBank; IF OldBank THEN EXECUTE IMMEDIATE UpdateTran USING COALESCE(OLDROW._Income,0),COALESCE(OLDROW._Expenditure,0),+ OLDROW._fkBanks ; END IF; END IF; END IF; END The next bit to bite me was OPERATION is case sensitive - as I found out when I read the OLH |
This web page was last updated on Wednesday, April 24, 2024 at 11:07 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |