MYSQL – TRIGGER 结合 IF + INSERT INTO

huangapple
go评论102阅读模式
英文:

MYSQL - TRIGGER combined with IF + INSERT INTO

问题

我的代码故意没有主键或外键，所以我正在寻找一种不添加它们的解决方案。
我的错误是：

错误代码：1054. 未知列 'cedula' 在 '字段列表' 中

触发器是问题的原因，因为当我删除触发器时，它能够正常工作，所以我不知道如何创建触发器。我希望触发器能够在名为 audit1 的表中存储插入到 personas表 的重复数据，以便我可以知道哪些ID在表 personas 中出现两次或更多次。

希望您能帮助我。我将我的所有代码都写在这里。

  1. CREATE DATABASE auditoria;
  2. USE auditoria;
  4. CREATE TABLE personas
  5. (
  6.     cedula BIGINT,
  7.     nombre VARCHAR(30),
  8.     codciudadnacimiento INT,
  9.     codactividadlaboral INT
  10. );
  12. CREATE TABLE ciudadesnacimiento
  13. (
  14.     codciudadnacimiento INT,
  15.     nombreciudadnacimiento VARCHAR(30)
  16. );
  18. CREATE TABLE actividadeslaborales
  19. (
  20.     codactividadlaboral INT,
  21.     nombreactividadlaboral VARCHAR(30)
  22. );
  24. CREATE TABLE audit1
  25. (
  26.     cedula BIGINT,
  27.     nombre VARCHAR(30),
  28.     observacion VARCHAR(30),
  29.     fecha DATETIME
  30. );
  32. DELIMITER //
  34. CREATE TRIGGER SPT1
  35. AFTER INSERT ON personas
  36. FOR EACH ROW
  37. BEGIN
  38.     IF (cedula = NEW.cedula)
  39.     THEN 
  40.     INSERT INTO audit1  VALUES
  41.     (NEW.cedula, NEW.nombre, 'DUPLICATE ID', NOW());
  42.     END IF;
  43. END //
  44. DELIMITER ;
  46. INSERT INTO ciudadesnacimiento VALUES
  47. (1, 'Bogota'),
  48. (3, 'Cartagena');
  50. INSERT INTO actividadeslaborales VALUES
  51. (1, 'Medico'),
  52. (2, 'Abogado');
  54. INSERT INTO personas VALUES
  55. (100,'Megadeth', 1, 1),
  56. (100,'Megadeth', 1, 1),
  57. (100,'Megadeth', 1, 1),
  58. (200,'Babasonicos', 2, 2),
  59. (200,'Babasonicos', 2, 2),
  60. (200,'Babasonicos', 2, 2),
  61. (300,'Spinetta', 3, 3),
  62. (400,'Paez', 3, 3),
  63. (500,'Charly', 3, 3),
  64. (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.

  1. CREATE DATABASE auditoria;
  2. USE auditoria;
  4. CREATE TABLE personas
  5. (
  6.     cedula BIGINT,
  7.     nombre VARCHAR(30),
  8.     codciudadnacimiento INT,
  9.     codactividadlaboral INT
  10. );
  12. CREATE TABLE ciudadesnacimiento
  13. (
  14.     codciudadnacimiento INT,
  15.     nombreciudadnacimiento 	VARCHAR(30)
  16. );
  18. CREATE TABLE actividadeslaborales
  19. (
  20.     codactividadlaboral INT,
  21.     nombreactividadlaboral VARCHAR(30)
  22. );
  24. CREATE TABLE audit1
  25. (
  26.     cedula BIGINT,
  27.     nombre VARCHAR(30),
  28.     observacion VARCHAR(30),
  29.     fecha DATETIME
  30. );
  32. DELIMITER //
  34. CREATE TRIGGER SPT1
  35. AFTER INSERT ON personas
  36. FOR EACH ROW
  37. BEGIN
  38. 	IF (cedula = NEW.cedula)
  39.     THEN 
  40.     INSERT INTO audit1  VALUES
  41.     (NEW.cedula, NEW.nombre, 'DUPLICATE ID', NOW());
  42. 	END IF;
  43. END //
  44. DELIMITER ;
  46. INSERT INTO ciudadesnacimiento VALUES
  47. (1, 'Bogota'),
  48. (3, 'Cartagena');
  50. INSERT INTO actividadeslaborales VALUES
  51. (1, 'Medico'),
  52. (2, 'Abogado');
  54. INSERT INTO personas VALUES
  55. (100,'Megadeth', 1, 1),
  56. (100,'Megadeth', 1, 1),
  57. (100,'Megadeth', 1, 1),
  58. (200,'Babasonicos', 2, 2),
  59. (200,'Babasonicos', 2, 2),
  60. (200,'Babasonicos', 2, 2),
  61. (300,'Spinetta', 3, 3),
  62. (400,'Paez', 3, 3),
  63. (500,'Charly', 3, 3),
  64. (500,'Charly', 3, 3);

I want to be able to insert data into personas table without that mistake.

答案1

得分: 1

以下是您需要的中文翻译：

似乎您需要的是

  1. CREATE TRIGGER SPT1
  2. BEFORE INSERT ON personas
  3. FOR EACH ROW
  4. BEGIN
  5.     IF EXISTS (SELECT NULL FROM personas WHERE cedula = NEW.cedula)
  6.     THEN 
  7.         INSERT INTO audit1  VALUES
  8.         (NEW.cedula, NEW.nombre, '重复的ID', NOW());
  9.     END IF;
  10. END

您可以在 https://dbfiddle.uk/94TG_YmB 上查看示例。

英文:

It seems that you need in

  1. CREATE TRIGGER SPT1
  2. BEFORE INSERT ON personas
  3. FOR EACH ROW
  4. BEGIN
  5.     IF EXISTS (SELECT NULL FROM personas WHERE cedula = NEW.cedula)
  6.     THEN 
  7.         INSERT INTO audit1  VALUES
  8.         (NEW.cedula, NEW.nombre, 'DUPLICATE ID', NOW());
  9.     END IF;
  10. END

https://dbfiddle.uk/94TG_YmB

huangapple
  • 本文由 发表于 2023年6月13日 07:47:46
  • 转载请务必保留本文链接：https://go.coder-hub.com/76460907.html
  • if-statement
  • mysql
  • sql-insert
  • triggers
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定