i want to do trigger code with import updates and inserts (table doctors to table logs(all operations when i update or delete)
DROP database IF EXISTS vk;
CREATE database vk;
USE vk;
DROP table IF EXISTS doctors;
create table doctors(
id int unsigned primary key not null unique auto_increment,
name varchar(15) comment "Имя" not null,
sacond_name varchar(50) comment "Фамилия" not null,
patronimic varchar(50) comment "Отчество"
);
alter table doctors add telephone int unsigned;
CREATE TABLE IF NOT EXISTS logs (
log_id INT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(255),
operation_type VARCHAR(10),
old_data TEXT,
new_data TEXT,
operation_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
DELIMITER ;;
CREATE TRIGGER doctors_update AFTER INSERT ON logs
FOR EACH ROW BEGIN
end;;
-- this trigger is work but it dont import data to table logs
DELIMITER $$
CREATE TRIGGER Doctor_insert_trig AFTER INSERT ON doctors FOR EACH row
BEGIN
INSERT INTO logs (table_name, operation_type, new_data, operation_date)
VALUES ('doctors', 'INSERT',
CONCAT('{"id": ', NEW.id, ', "name": "', NEW.name, '", "sacond_name": "', NEW.sacond_name, '", "patronimic": "', NEW.patronimic, '"}'),
NOW());
end;
DELIMITER ;
-- I already tried to do this with the import, but an error was written
SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6
Error position: line: 5
made trigger to import delete on updates data of table doctors to table logs
DELIMITER $$- where does the trigger creation code is terminated with this delimiter?