MySQL trigger to UPDATE entire table before INSERT

Question

I want to create a trigger that updates a particular attribute for all the tuples of a table, before a new tuple is inserted.

So whenever I run INSERT INTO Employee VALUES (...); I want the salary of all previously stored employees to be decreased by 300, that is, UPDATE Employee SET Salary = Salary - 300;

I'm really new to SQL so I naively tried the following:

CREATE TRIGGER reducesalary BEFORE INSERT ON Employee
FOR EACH ROW
UPDATE Employee SET Salary = Salary - 300;

Of course, I then found out this is incorrect since triggers cannot work on the same table that calls them. How do I go about doing this?

Why would you reduce the salary automatically if a new employee is added? — juergen d, Mar 20 '14 at 12:15
It's not a practical thing, but just suppose there is a real-world condition that requires reduction in everyone's salary whenever a new employee is hired. — Aakash Jain, Mar 20 '14 at 12:18

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

Has been already asked here,

MySQL - Trigger for updating same table after insert

You need to use stored procedures

edited May 23 '17 at 11:49

Community

1
1

answered Mar 20 '14 at 13:10

AlexF

487
1
5
16

MySQL trigger to UPDATE entire table before INSERT

1 Answers1