Below is the code I am using, everytime I try to update the salary I get
“ERROR: infinite recursion detected in rules for relation “employees”
SQL state: 42P17″
Object used
<code>create table rowan.employees (emp_id serial primary key, name varchar(100), salary numeric);
</code>
<code>create table rowan.employees (emp_id serial primary key, name varchar(100), salary numeric);
</code>
create table rowan.employees (emp_id serial primary key, name varchar(100), salary numeric);
Inserting values
<code>insert into rowan.employees (name, salary) values
('John Doe', 50000),
('Jane Doe', 60000),
('Rafael Orta',80000);
</code>
<code>insert into rowan.employees (name, salary) values
('John Doe', 50000),
('Jane Doe', 60000),
('Rafael Orta',80000);
</code>
insert into rowan.employees (name, salary) values
('John Doe', 50000),
('Jane Doe', 60000),
('Rafael Orta',80000);
Creation of the rule
<code>create or replace rule update_salary
as on update to rowan.employees
where new.salary > 70000
and pg_trigger_depth() = 0
do instead
update rowan.employees
set salary = 70000
where emp_id = new.emp_id;
</code>
<code>create or replace rule update_salary
as on update to rowan.employees
where new.salary > 70000
and pg_trigger_depth() = 0
do instead
update rowan.employees
set salary = 70000
where emp_id = new.emp_id;
</code>
create or replace rule update_salary
as on update to rowan.employees
where new.salary > 70000
and pg_trigger_depth() = 0
do instead
update rowan.employees
set salary = 70000
where emp_id = new.emp_id;
Performing the Update
<code>update rowan.employees set salary = 80000 where emp_id = 3;
</code>
<code>update rowan.employees set salary = 80000 where emp_id = 3;
</code>
update rowan.employees set salary = 80000 where emp_id = 3;
Checking the values
<code>select * from rowan.employees;
</code>
<code>select * from rowan.employees;
</code>
select * from rowan.employees;
I am trying to do the command below
<code>update rowan.employees set salary = 80000 where emp_id = 3;
</code>
<code>update rowan.employees set salary = 80000 where emp_id = 3;
</code>
update rowan.employees set salary = 80000 where emp_id = 3;
I was expecting for it to update the salary to be 70000. Any help will be apprecited.
New contributor
Rafael Orta is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.