can u explain why this isn't working? error code 1442
delimiter $$
create trigger newwydate
after insert on employees
for each row
begin
declare dateeeeee date;
select hire_date into dateeeeee from employees
where hire_date=new.hire_date;
if dateeeeee >date_format(sysdate(),'%y-%d-%m') then update employees set hire_date=date_format(sysdate(),'%y-%d-%m') where emp_no=new.emp_no;
end if;
end $$
delimiter ;
insert into employees values("333",'2000-11-5','rambo',"stalone","M","2025-01-01");
I tried running your code and the trigger creates fine. However the insert statement errors out due to quotation marks you have used.
Hi Madhav and Tanika!
Thanks for reaching out.
@: Tanika
Actually, this is not an issue. Both quotes can be used.
@: Madhav
In our code, we use a BEFORE INSERT, here you are trying to apply an AFTER INSERT trigger, where the table should be updated if a new insert, as indicated by IF NEW.hire_date > ... satisfies the condition to come.
But if you are referring to the existing records, then what would be NEW?
That's why the software tells you it can't update the content of the table because of the content that has invoked the procedure (and this is the existing content, i.e. the existing records).
Hope this helps.
Best,
Tsvetelin