0

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

1
  • DELIMITER $$ - where does the trigger creation code is terminated with this delimiter? Commented Dec 22, 2024 at 19:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.