mysql触发器 - 如何在插入后添加记录ID

Question

我有一个日志表:

mysql> desc MyLog;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| Aud_ID      | int(11)      | NO   | PRI | NULL    | auto_increment |
| Rec_ID      | int(11)      | NO   |     | NULL    |                |
| AudObj      | varchar(255) | NO   |     | NULL    |                |
| User_ID     | varchar(255) | NO   |     | NULL    |                |
| AudType     | varchar(15)  | NO   |     | NULL    |                |
| ChangedOn   | datetime     | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

我有一个“插入后”的触发器:

CREATE TRIGGER after_Images_add
    AFTER INSERT ON Images_TST
    FOR EACH ROW 
    BEGIN
       INSERT INTO AuditLog
       SET Rec_ID = NEW.Image_ID,  
           AudType = 'ADDED', 
           AudObj = 'Images', 
           User_ID = NEW.employee_ID,
           changedon = NOW();
    END;

在“插入后”，当我们向表中添加新记录时，我的 Rec_ID(尚不存在，直到添加 = 0)，但在添加后，我想捕获“NEW "Rec_ID 并将其插入表中。我可以在同一个触发器中执行此操作吗？

这是我的输出:

mysql> select * from MyLog;
+----------+-----------+-------------+---------+-----------+---------------------+
| Aud_ID   | Rec_ID    | AudObj      | User_ID | AudType   | ChangedOn           |
+----------+-----------+-------------+---------+-----------+---------------------+
|     1003 |         0 | Images      | 445     | ADDED     | 2014-10-28 09:09:42 |
+----------+-----------+-------------+---------+-----------+---------------------+

这是我正在寻找的输出:

+----------+-----------+-------------+---------+-----------+---------------------+
| Aud_ID   | Rec_ID    | AudObj      | User_ID | AudType   | ChangedOn           |
+----------+-----------+-------------+---------+-----------+---------------------+
|     1003 |         0 | Images      | 445     | ADDED     | 2014-10-28 09:09:42 |
|     1004 |         22| Images      | 445     | NEWREC_ID | 2014-10-28 09:09:42 |
+----------+-----------+-------------+---------+-----------+---------------------+

如有任何建议，我们将不胜感激。

Answer 1

您应该能够按照您尝试的方式进行操作。只需像下面这样使用 INSERT INTO TABLE_NAME .. VALUES 构造稍微修改触发器主体

CREATE TRIGGER after_Images_add
    AFTER INSERT ON Images_TST
    FOR EACH ROW 
    BEGIN
       INSERT INTO MyLog(Rec_ID, AudType, AudObj, User_ID, changedon)
       VALUES(NEW.Image_ID, 'ADDED', 'Images', NEW.employee_ID, NOW());
    END;