Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 3 of 3 total
Thread OLDROW NEWROW
Tue, Feb 12 2019 10:46 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email 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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate 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 SmileyI 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
Image