英文:
MYSQL - TRIGGER combined with IF + INSERT INTO
问题
我的代码故意没有主键或外键，所以我正在寻找一种不添加它们的解决方案。
我的错误是：
错误代码：1054. 未知列 'cedula' 在 '字段列表' 中
触发器是问题的原因，因为当我删除触发器时，它能够正常工作，所以我不知道如何创建触发器。我希望触发器能够在名为 audit1 的表中存储插入到 personas表 的重复数据，以便我可以知道哪些ID在表 personas 中出现两次或更多次。
希望您能帮助我。我将我的所有代码都写在这里。
CREATE DATABASE auditoria;
USE auditoria;
CREATE TABLE personas
(
cedula BIGINT,
nombre VARCHAR(30),
codciudadnacimiento INT,
codactividadlaboral INT
);
CREATE TABLE ciudadesnacimiento
(
codciudadnacimiento INT,
nombreciudadnacimiento VARCHAR(30)
);
CREATE TABLE actividadeslaborales
(
codactividadlaboral INT,
nombreactividadlaboral VARCHAR(30)
);
CREATE TABLE audit1
(
cedula BIGINT,
nombre VARCHAR(30),
observacion VARCHAR(30),
fecha DATETIME
);
DELIMITER //
CREATE TRIGGER SPT1
AFTER INSERT ON personas
FOR EACH ROW
BEGIN
IF (cedula = NEW.cedula)
THEN
INSERT INTO audit1 VALUES
(NEW.cedula, NEW.nombre, 'DUPLICATE ID', NOW());
END IF;
END //
DELIMITER ;
INSERT INTO ciudadesnacimiento VALUES
(1, 'Bogota'),
(3, 'Cartagena');
INSERT INTO actividadeslaborales VALUES
(1, 'Medico'),
(2, 'Abogado');
INSERT INTO personas VALUES
(100,'Megadeth', 1, 1),
(100,'Megadeth', 1, 1),
(100,'Megadeth', 1, 1),
(200,'Babasonicos', 2, 2),
(200,'Babasonicos', 2, 2),
(200,'Babasonicos', 2, 2),
(300,'Spinetta', 3, 3),
(400,'Paez', 3, 3),
(500,'Charly', 3, 3),
(500,'Charly', 3, 3);
我希望能够在不出错的情况下向 personas 表中插入数据。
英文:
everyone
My code doesn't have primary key nor foreign key on purpose so I'm looking for a solution without adding that.
My error is:
> Error Code: 1054. Unknown column 'cedula' in 'field list'
The trigger is the problem because when I drop the trigger it works well so I don't know how to create the trigger. What I want with the trigger is to have in a table called audit1 the duplicate data that is INSERT INTO personas table so I can tell which ID is twice or more in the table personas.
Hope you can help me. I write my all code down here.
CREATE DATABASE auditoria;
USE auditoria;
CREATE TABLE personas
(
cedula BIGINT,
nombre VARCHAR(30),
codciudadnacimiento INT,
codactividadlaboral INT
);
CREATE TABLE ciudadesnacimiento
(
codciudadnacimiento INT,
nombreciudadnacimiento VARCHAR(30)
);
CREATE TABLE actividadeslaborales
(
codactividadlaboral INT,
nombreactividadlaboral VARCHAR(30)
);
CREATE TABLE audit1
(
cedula BIGINT,
nombre VARCHAR(30),
observacion VARCHAR(30),
fecha DATETIME
);
DELIMITER //
CREATE TRIGGER SPT1
AFTER INSERT ON personas
FOR EACH ROW
BEGIN
IF (cedula = NEW.cedula)
THEN
INSERT INTO audit1 VALUES
(NEW.cedula, NEW.nombre, 'DUPLICATE ID', NOW());
END IF;
END //
DELIMITER ;
INSERT INTO ciudadesnacimiento VALUES
(1, 'Bogota'),
(3, 'Cartagena');
INSERT INTO actividadeslaborales VALUES
(1, 'Medico'),
(2, 'Abogado');
INSERT INTO personas VALUES
(100,'Megadeth', 1, 1),
(100,'Megadeth', 1, 1),
(100,'Megadeth', 1, 1),
(200,'Babasonicos', 2, 2),
(200,'Babasonicos', 2, 2),
(200,'Babasonicos', 2, 2),
(300,'Spinetta', 3, 3),
(400,'Paez', 3, 3),
(500,'Charly', 3, 3),
(500,'Charly', 3, 3);
I want to be able to insert data into personas table without that mistake.
答案1
得分: 1
以下是您需要的中文翻译：
似乎您需要的是
CREATE TRIGGER SPT1
BEFORE INSERT ON personas
FOR EACH ROW
BEGIN
IF EXISTS (SELECT NULL FROM personas WHERE cedula = NEW.cedula)
THEN
INSERT INTO audit1 VALUES
(NEW.cedula, NEW.nombre, '重复的ID', NOW());
END IF;
END
您可以在 https://dbfiddle.uk/94TG_YmB 上查看示例。
英文:
It seems that you need in
CREATE TRIGGER SPT1
BEFORE INSERT ON personas
FOR EACH ROW
BEGIN
IF EXISTS (SELECT NULL FROM personas WHERE cedula = NEW.cedula)
THEN
INSERT INTO audit1 VALUES
(NEW.cedula, NEW.nombre, 'DUPLICATE ID', NOW());
END IF;
END
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论