CREATE TRIGGER `table_triger_on_before_delete`
BEFORE DELETE ON `db`.`table`
FOR EACH ROW
INSERT INTO db_bkp.`table`
(col1, col2, col3)
VALUES
(OLD.col1, OLD.col2, OLD.col3)
I want to get this
(col1, col2, col3)
without type them one by one
If you're absolutely sure that the tables are with the same fields you can use simply that
CREATE TRIGGER `table_triger_on_before_delete`
BEFORE DELETE ON `db`.`table`
FOR EACH ROW
INSERT INTO db_bkp.`table`
SELECT * FROM `db`.`table` WHERE `you_primary_key`= OLD.`you_primary_key`
Becouse you row isn't deleted yet :)
It is possible to query the list of columns of a particular table from information_schema.columns table within MySQL.
However, in this case you need to create your insert statement using prepared statement because this allows you to create the sql command as a string and then you can execute it.
...
declare s_fields, s_sql varchar(1000); --length should depend on how many fields you have
select group_concat(column_name) into s_fields
from information_schema.columns
where table_name='yourtable'
group by table_name
limit 1;
set s_sql=concat('insert into tablename (',s_fields, ') values (', variable_holding_comma_separated_list_of_values,')');
prepare stmt from s_sql;
execute stmt;
deallocate prepare stmt;
...
I have modified the code from @Shadow. you must also include the schema name. and also put it in backquotes to prevent spaces in the fieldnames.
...
DECLARE s_sql VARCHAR(1000); --length should depend ON how many fields you have
SELECT
CONCAT(
'insert into tablename ('
,GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`'))
,') values ('
,GROUP_CONCAT(CONCAT('`OLD`.`',COLUMN_NAME,'`')),
')'
)
INTO s_sql
FROM information_schema.columns
WHERE
TABLE_NAME='your_table'
AND
TABLE_SCHEMA='your_schema'
GROUP BY TABLE_NAME;
prepare stmt from s_sql;
execute stmt;
deallocate prepare stmt;
...
sample
MariaDB [your_schema]> SELECT
-> CONCAT(
-> 'insert into tablename ('
-> ,GROUP_CONCAT(CONCAT('`',COLUMN_NAME,'`'))
-> ,') values ('
-> ,GROUP_CONCAT(CONCAT('`OLD`.`',COLUMN_NAME,'`')),
-> ')'
-> )
-> INTO @s_sql
-> FROM information_schema.columns
-> WHERE
-> TABLE_NAME='your_table'
-> AND
-> TABLE_SCHEMA='your_schema'
-> GROUP BY TABLE_NAME;
Query OK, 1 row affected (0.00 sec)
MariaDB [your_schema]> select @s_sql;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @s_sql |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| insert into tablename (`sr_no`,`scholar_no`,`paid_amount`,`due_amount`,`fee_date`,`section_id_fk`) values (`OLD`.`sr_no`,`OLD`.`scholar_no`,`OLD`.`paid_amount`,`OLD`.`due_amount`,`OLD`.`fee_date`,`OLD`.`section_id_fk`) |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [your_schema]>
