Unknown System Variable - MySQL

Question

The student table looks like this

Name varchar(255), sub1 int(11), sub2 int(11), sub3 int(11)

I created a trigger to update sub3 based on the sum of sub1 and sub2,

create Trigger subject_total
after INSERT
on
student
for each row
set sub3 = sub1 + sub2;

While executing the above code, the following error is encountered. Error Code: 1193. Unknown system variable 'sub3'

DB: MYSQL

Unfortunately, this looks like a poor place to start. A database table is not a spreadsheet, and any time you find yourself with enumerated column names (or columns like 'Maths', 'French', 'English', which are all essentially holding the same kind of thing) alarm bells should start ringing. Consider revising your schema accordingly. — Strawberry
– Strawberry, Commented Jan 29, 2020 at 11:53

GMB · Accepted Answer · 2020-01-29 11:19:03Z

3

I think that you want a before insert trigger that sets the value of sub3.

That would be:

create trigger subject_total
before insert on student
for each row
set new.sub3 = new.sub1 + new.sub2;

Within the trigger, you can access the values that are being inserted using pseudo-table new.

answered Jan 29, 2020 at 11:19

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Akina · Accepted Answer · 2020-01-29 11:29:26Z

2

If sub3 must be ALWAYS equal to the sum of sub1 and sub2 I'd recommend to remove static field and replace it with calculated one:

ALTER TABLE subject_total
DROP COLUMN sub3,
ADD COLUMN sub3 INT GENERATED ALWAYS AS (sub1 + sub2) STORED;

And you do not need in the trigger at all.

answered Jan 29, 2020 at 11:29

Akina

43.2k6 gold badges17 silver badges30 bronze badges

1 Comment

GMB Over a year ago

I am unsure why this answer was downvoted - in my opinion it does provide a solution to the question (although not using a trigger). FYI my answer was anonymously downvoted too.

Strawberry · Accepted Answer · 2020-01-29 11:56:05Z

0

Revise your schema as follows:

student
student_id, student_details, etc

student_marks
student_id, subject_id, score

Now the problem is trivial, and note that is no benefit here in storing derived data, and hence no need for a trigger.

answered Jan 29, 2020 at 11:56

Strawberry

34k14 gold badges43 silver badges58 bronze badges

Collectives™ on Stack Overflow

Unknown System Variable - MySQL

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related