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
CodePudding user response：
- 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.
