SQL Trigger Wont Generate Primary Key

user_11445583 0
Viewed 57

I am trying to create a table that logs all inserts in the author table. Here is the author table, and the Audit_log Table:

CREATE TABLE Author(AuthorID INT PRIMARY KEY NOT NULL,
                    last_name CHAR(20),
                    first_name CHAR(20));

CREATE TABLE Audit_Log(Action_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
                       table_name Char(40), 
                       action_name Char(6),
                       Date_Time DATETIME DEFAULT CURRENT_TIMESTAMP);

Here is the simple trigger.

DELIMITER $$ 
CREATE TRIGGER Author_Trigger AFTER INSERT
    ON Author
    FOR EACH ROW BEGIN
    INSERT INTO Audit_Log VALUES('Author', 'INSERT', CURRENT_TIMESTAMP);
    END $$
DELIMITER ;

However, when I cause the trigger to occur by inserting into the author table, it says that the columns do not match row 1. How come my Primary key does not get auto generated, despite having the AUTO_INCREMENT Constraint?

How can I get this trigger to generate the primary key?

primary-key triggers sql
2 Answers

Basically MySQL thinks that 'Author' is being inserted as the PRIMARY KEY (Action_ID). What you can do to avoid this is specify wich attribute correspond to wich element of the INSERT query. This should work :)

DELIMITER $$ 
CREATE TRIGGER Author_Trigger AFTER INSERT
    ON Author
    FOR EACH ROW BEGIN
    INSERT INTO Audit_Log(table_name, action_name, Date_Time) 
           VALUES('Author', 'INSERT', CURRENT_TIMESTAMP);
    END $$
DELIMITER ;
user_147618110

For the value to autoincrement, since you do not have defined the data you must insert and place the autoincremental, it does not take it, so the first value is null and through this null it will autoincrement.

DELIMITER $$ 
CREATE TRIGGER Author_Trigger AFTER INSERT
    ON Author
    FOR EACH ROW BEGIN
    INSERT INTO Audit_Log VALUES(null,'Author', 'INSERT', CURRENT_TIMESTAMP);
    END $$
DELIMITER ;
user_124648800
Related