DROP TABLE IF EXISTS Sales;

CREATE TABLE Sales (
    id INT AUTO_INCREMENT,
    product VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    fiscalYear SMALLINT NOT NULL,
    fiscalMonth TINYINT NOT NULL,
    CHECK(fiscalMonth >= 1 AND fiscalMonth <= 12),
    CHECK(fiscalYear BETWEEN 2000 and 2050),
    CHECK (quantity >=0),
    UNIQUE(product, fiscalYear, fiscalMonth),
    PRIMARY KEY(id)
);

DROP TABLE IF EXISTS log;

CREATE TABLE log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
    text VARCHAR(100)
);

Triggers

CREATE DEFINER=`root`@`localhost` TRIGGER `sales_AFTER_UPDATE` AFTER UPDATE ON `sales` 
FOR EACH ROW 
BEGIN
INSERT INTO log VALUES(NOW(),CONCAT('Update Student Record ', OLD.quantity));
END

UPDATE test for.sales SET quantity = 36 WHERE (id = 1);

ERROR 1136: 1136: Column count doesn't match value count at row 1

Iam new in mySQL Please help

CodePudding user response：

You should specify columns in INSERT statement in your trigger explicitly, as you do not set all values in a row (auto incremented column excluded).

So it would be

INSERT INTO log(timestamp, text) VALUES (NOW(),CONCAT('Update Student Record ', OLD.quantity));

CodePudding user response：

You have some errors. First based on your trigger you need another column on log table which is as following

quantity INT NOT NULL DEFAULT 0

Second , do not use Keywords and Reserved Words like text and timestamp, it is a bad practice. If you do please put it inside backticks

Third your insert statement should be

INSERT INTO log(`timestamp`,`text`,quantity) VALUES(NOW(),'Update Student Record', OLD.quantity);

there is no need for CONCAT.

Fourth,

`sales` 

table is not the same as Sales table, because you have used backticks.

Full working trigger below:

DELIMITER //
CREATE  TRIGGER `sales_AFTER_UPDATE` AFTER UPDATE ON Sales
FOR EACH ROW 
BEGIN
INSERT INTO log(`timestamp`,`text`,quantity) VALUES(NOW(),'Update Student Record', OLD.quantity);
END//
DELIMITER ;

Check working demo: https://www.db-fiddle.com/f/iqwShcHK3AGJvU4MDbxDku/0