CODEWITHSUNDEEP
mysqlsqltriggers
Siva Rama krishna Kumar
Siva Rama krishna Kumar

Reputation: 43

failed to create trigger Error Code: 1363. There is no OLD row in on INSERT trigger

I am using these as a practice

Main table:

create table emp 
(
    emp_id int(5) primary key,
    e_name varchar(30),
    e_title varchar(20),
    e_salary decimal(10, 2)
);

Log/audit table:

create table emp_log
(
    tracking_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    change_type varchar(30),
    change_occured_at VARCHAR(50) NOT NULL,
    old_value INT NOT NULL,
    new_value INT NOT NULL,
    modified DATETIME NOT NULL,
    created_by varchar(50)
);

After creating these tables, I was trying to create triggers for the main table to create a log in the log table I tried these triggers all my triggers should work after changes made by the {INSERT|UPDATE|DELETE}

Here is my trigger code; from what I understand from the example presented in the internet, the triggers I have are all after triggers

DELIMITER $$
create trigger ai_emp_log after insert on emp for each row
begin
DECLARE vUser varchar(50);
select user() into vUser;
if (new.e_name!=old.e_name) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('insert',emp.emp_id,'record_not_present',new.emp.e_name,SYSDATE(),vUser);
end if;
if (new.e_title!=old.e_title) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('insert',emp.emp_id, 'record_not_present',new.emp.e_tile,SYSDATE(),vUser);
end if;
if (new.e_salary!=old.e_salary) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('insert',emp.emp_id, 'record_not_present',new.emp.e_salary,SYSDATE(),vUser);
end if;
end;
DELIMITER;

DELIMITER $$
create trigger au_emp_log after update on emp for each row
begin
DECLARE vUser varchar(50);
select user() into vUser;
if (new.e_name!=old.e_name) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('update',emp.emp_id, old.e_name,new.e_name,SYSDATE(),vUser);
end if;
if (new.e_title!=old.e_title) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('update',emp.emp_id, old.e_title,new.e_tile,SYSDATE(),vUser);
end if;
if (new.e_salary!=old.e_salary) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('update',emp.emp_id, old.e_salary,new.e_salary,SYSDATE(),vUser);
end if;
end;
DELIMITER;

DELIMITER $$
create trigger ad_emp_log after delete on emp for each row
begin
DECLARE vUser varchar(50);
select user() into vUser;
if (new.e_name!=old.e_name) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('delete',emp.emp_id, old.e_name,'record_deleted',SYSDATE(),vUser);
end if;
if (new.e_title!=old.e_title) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('delete',emp.emp_id, old.e_title,'record_deleted',SYSDATE(),vUser);
end if;
if (new.e_salary!=old.e_salary) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('delete',emp.emp_id, old.e_salary,'record_deleted',SYSDATE(),vUser);
end if;
end;
DELIMITER;

help me by correcting the mistakes I made in the trigger code as I get these error when I try to run them this is the output log from MySQL when I tried to run the code

16:41:32 create trigger au_emp_log after update on emp for each row begin DECLARE vUser varchar(50); select user() into vUser; if (new.e_name!=old.e_name) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('update',emp.emp_id, old.e_name,new.e_name,SYSDATE(),vUser); end if; if (new.e_title!=old.e_title) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('update',emp.emp_id, old.e_title,new.e_tile,SYSDATE(),vUser); end if; if (new.e_salary!=old.e_salary) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('update',emp.emp_id, old.e_salary,new.e_salary,SYSDATE(),vUser); end if; end; DELIMITER; Error Code: 1054. Unknown column 'e_tile' in 'NEW' 0.140 sec

16:41:46 create trigger ad_emp_log after delete on emp for each row begin DECLARE vUser varchar(50); select user() into vUser; if (new.e_name!=old.e_name) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('delete',emp.emp_id, old.e_name,new.e_name,SYSDATE(),vUser); end if; if (new.e_title!=old.e_title) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('delete',emp.emp_id, old.e_title,new.e_tile,SYSDATE(),vUser); end if; if (new.e_salary!=old.e_salary) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('delete',emp.emp_id, old.e_salary,new.e_salary,SYSDATE(),vUser); end if; end; DELIMITER; Error Code: 1363. There is no NEW row in on DELETE trigger 0.000 sec

16:44:50 create trigger ai_emp_log after insert on emp for each row begin DECLARE vUser varchar(50); select user() into vUser; if (new.e_name!=old.e_name) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('insert',emp.emp_id,'record_not_present',new.emp.e_name,SYSDATE(),vUser); end if; if (new.e_title!=old.e_title) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('insert',emp.emp_id, 'record_not_present',new.emp.e_tile,SYSDATE(),vUser); end if; if (new.e_salary!=old.e_salary) then insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) values('insert',emp.emp_id, 'record_not_present',new.emp.e_salary,SYSDATE(),vUser); end if; end; DELIMITER; Error Code: 1363. There is no OLD row in on INSERT trigger 0.000 sec

Upvotes: 0

Views: 1386

Answers (2)

Siva Rama krishna Kumar
Siva Rama krishna Kumar

Reputation: 43

after a few more digging around i had the trigger working. so thought I would share it with you guys who might face a similar situation might be helpful for them or work as an example/reference for their work

after insert trigger

DELIMITER $$
create trigger ai_emp_log after insert on emp for each row
begin
DECLARE vUser varchar(50);
select user() into vUser;

insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('insert',new.emp_id,"no old value",new.e_name,SYSDATE(),vUser);

insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('insert',new.emp_id,"no old value",new.e_title,SYSDATE(),vUser);

insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('insert',new.emp_id,"no old value",new.e_salary,SYSDATE(),vUser);

end;
DELIMITER;

after update trigger

DELIMITER $$
create trigger au_emp_log after update on emp for each row
begin
DECLARE vUser varchar(50);
select user() into vUser;
if (new.e_name!=old.e_name) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('update',new.emp_id, old.e_name,new.e_name,SYSDATE(),vUser);
end if;
if (new.e_title!=old.e_title) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('update',new.emp_id, old.e_title,new.e_title,SYSDATE(),vUser);
end if;
if (new.e_salary!=old.e_salary) then
insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('update',emp_id, old.e_salary,new.e_salary,SYSDATE(),vUser);
end if;
end;
DELIMITER;

after delete trigger

DELIMITER $$
create trigger ad_emp_log after delete on emp for each row
begin
DECLARE vUser varchar(50);
select user() into vUser;

insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('delete',old.emp_id, old.e_name,"value deleted",SYSDATE(),vUser);

insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('delete',old.emp_id, old.e_title,"value deleted",SYSDATE(),vUser);

insert into emp_log(change_type,change_occured_at,old_value,new_value,modified,created_by) 
values('delete',old.emp_id, old.e_salary,"value deleted",SYSDATE(),vUser);

end;
DELIMITER;

Here the output of the test I done

output of the test

Upvotes: 0

cnt
cnt

Reputation: 11

  1. ai_emp_log - We don't have 'OLD' row in insert trigger. We only have 'NEW' row there.

2.au_emp_log - Its e_title, not 'e_tile'

3.ad_emp_log - We don't have 'NEW' row in delete trigger. We are removing a row, not creating new one.

Refer this to get an idea about triggers. https://phoenixnap.com/kb/mysql-trigger

Upvotes: 1

Related Questions