I'm having an issue with not being able to insert new records on a MySQL table that has a trigger, due to how the trigger is written. I get the error:
Cardinality violation: 1241 Operand should contain 1 column(s)
It's essentially an audit trigger, copying new records to an audit table with additional data. I've successfully demonstrated that the issue is specific to the nested 'SELECT a.*...' in the VALUES() section of the trigger's Insert, since when I replace that SELECT with an itemized list of 'NEW.xyx' columns it works fine. Examples below:
TableA:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
`admin` tinyint(1) NOT NULL DEFAULT 0,
`date_updated` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
TableB:
CREATE TABLE `users_audit` (
`action` varchar(8) NOT NULL,
`revision` int(11) NOT NULL,
`id` int(11) NOT NULL,
`username` varchar(20) NOT NULL,
`email` varchar(50) NOT NULL,
`admin` tinyint(1) NOT NULL,
`date_updated` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Trigger (causes Cardinality violation upon insert to TableA):
CREATE TRIGGER users_ins AFTER INSERT ON users
FOR EACH ROW BEGIN
INSERT INTO users_audit
VALUES ('insert',
(SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id),
(SELECT b.* FROM users b WHERE b.id = NEW.id LIMIT 1)
);
END
The issue is with the line (SELECT b.* FROM user_accounts b WHERE b.id = NEW.id LIMIT 1). There are no problems with the line above this (first 'select' statement). I even added the 'LIMIT 1' to ensure only 1 row was being returned (which it should because 'id' is a PK on 'users'). I believe the error is happening because the second SELECT statement returns multiple columns (due to 'SELECT b.*...') and they must not be getting comma delimited so that the number of items in 'VALUES' matches the number of colums in 'users_audit'.
I've tried removing the parenthesis from around the second select, like so:
CREATE TRIGGER users_ins AFTER INSERT ON users
FOR EACH ROW BEGIN
INSERT INTO users_audit
VALUES ('insert',
(SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id),
SELECT b.* FROM users b WHERE b.id = NEW.id LIMIT 1
);
END
but that doesn't even run in myPhpAdmin (I get a syntax error).
Is there really no way to do this and not have to manually write out each column? For the record, I've gotten the following to work but this is going to take a lot of extra time to do for all of the tables I'm adding auditing to (having to write out each of their columns manually).
Working trigger, but too verbose having to list the NEW columns individually:
CREATE TRIGGER users_ins AFTER INSERT ON users
FOR EACH ROW BEGIN
INSERT INTO users_audit
VALUES ('insert',
(SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id),
NEW.id, NEW.username, NEW.email, NEW.admin, NEW.date_updated
);
END
I'd really like to avoid having to write out the columns like that. Surely there must be some way to do what I'm looking for? Thanks for the help!
In general, depending on column order with select * and inserting without a column list is a very bad idea, so I would recommend just listing the new columns individually. In this case, I see you have two closely related tables users and users_audit where you can expect that they stay in sync. But you do risk errors if the trigger fires while you are adding new columns and they are in one table but not yet the other. That said, you should be able to do this with INSERT...SELECT instead of INSERT...VALUES:
INSERT INTO users_audit
SELECT
'insert',
(SELECT IFNULL(MAX(a.revision), 0) + 1 FROM users_audit a where a.id = NEW.id),
b.*
FROM users b WHERE b.id = NEW.id