2

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?

Aakash Jain
  • 1,883
  • 17
  • 28
  • 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

1 Answers1

1

Has been already asked here,

MySQL - Trigger for updating same table after insert

You need to use stored procedures

Community
  • 1
  • 1
AlexF
  • 488
  • 1
  • 5
  • 16