SQL Question

How to list all columns in table with Sql trigger

CREATE TRIGGER `table_triger_on_before_delete`
BEFORE DELETE ON `db`.`table`
INSERT INTO db_bkp.`table`
(col1, col2, col3)
(OLD.col1, OLD.col2, OLD.col3)

I want to get this

(col1, col2, col3)

without type them one by one

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download