Айдън Бейтулов Айдън Бейтулов - 5 months ago 10
SQL Question

How to list all columns in table with Sql trigger

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

Answer

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;
...