0
0 Comments

i have several tables declared as

CREATE TABLE Account (
AccountNumber INT (7),
BillNumber INT (255),
BillDate DATE,
DueDate DATE,
ClientNumber INT(255),
monthlybasefee DECIMAL (6,2),
modemratefee DECIMAL (6,2),
phonelinefee DECIMAL(6,2),
regulatorycharges DECIMAL(6,2),
BandwidthOveruse DECIMAL (6,2),
SubtotalofInternetServices DECIMAL (6,2),
LocalTax DECIMAL(6,3),
SubtotalwithTaxes DECIMAL (6,2),
PreviousBalance DECIMAL(6,2),
LastPayment DECIMAL (6,2),
BalanceForward DECIMAL (6,2),
LocalBenefit DECIMAL (6,2),
TotalCharge DECIMAL(6,2),
PRIMARY KEY (AccountNumber),
FOREIGN KEY (ClientNumber) REFERENCES Customer(Numberr)
);

CREATE TABLE PreviousAccounts(
AccountNumber INT (7),
BillNumber INT (255),
BillDate DATE,
DueDate DATE,
ClientNumber INT(255),
monthlybasefee DECIMAL (6,2),
modemratefee DECIMAL (6,2),
phonelinefee DECIMAL(6,2),
regulatorycharges DECIMAL(6,2),
BandwidthOveruse DECIMAL (6,2),
SubtotalofInternetServices DECIMAL (6,2),
LocalTax DECIMAL(6,3),
SubtotalwithTaxes DECIMAL (6,2),
PreviousBalance DECIMAL(6,2),
LastPayment DECIMAL (6,2),
BalanceForward DECIMAL (6,2),
LocalBenefit DECIMAL (6,2),
TotalCharge DECIMAL(6,2),
PRIMARY KEY (AccountNumber),
FOREIGN KEY (ClientNumber) REFERENCES Customer(Numberr)
);

CREATE TABLE Customer(
Namee VARCHAR (20) ,
Numberr INT (255),
Address VARCHAR (50),
ZIPCODE VARCHAR(10),
NoOfAccts INT(255),
PRIMARY KEY (Numberr)
);

then i have a trigger defined as

DELIMITER 
      \[CREATE TRIGGER upd_cust_trggr AFTER INSERT ON Account  FOR EACH ROW BEGIN CALL upd_customer_tbl(); END; </pre></div><!-- /wp:codemirror-blocks/code-block --> <!-- wp:paragraph -->which will call the procedure below when a row was inserted at the account table<!-- /wp:paragraph --> <!-- wp:codemirror-blocks/code-block {"showPanel":false,"languageLabel":"no","mode":"clike","mime":"text\/x-c++src"} --> 			<div class="wp-block-codemirror-blocks-code-block code-block"><pre>DELIMITER\]

CREATE PROCEDURE upd_customer_tbl()
BEGIN
UPDATE Customer
SET Customer.NoOfAccts=Customer.NoOfAccts+1
WHERE customer.Numberr=account.ClientNumber;
END;

but when i try to insert data in the account table by calling the code below;

the following error would be outputted.

Error Code: 1054. Unknown column ‘account.ClientNumber’ in ‘where clause’

how can i fix this? hoping for a kind, respectful answer. besides, im only a college student. so i dont know much about this.

Anonymous Asked question May 14, 2021