How to list all columns in table with Sql trigger

Question

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

Why would you want to do that? Just declare them otherwise you're going to get all types or errors with mismatching data. — Rich Benner, Jun 17 '16 at 09:05
As far as I know you can't trigger an insertion to another database without connecting them in the CREATE-Statement. In the following link is described how to connect databases on different servers. It may work for a single server also: http://stackoverflow.com/questions/26503342/how-to-create-trigger-to-insert-data-to-a-database-on-another-server — Otterbein, Jun 17 '16 at 09:14
Rich Benner cuz i have to make 800+ triggers i cant make them 1 by 1 — Айдън Бейтулов, Jun 17 '16 at 10:06

score 2 · Answer 1 · answered Jun 17 '16 at 10:34

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 :)

score 1 · Accepted Answer · answered Jun 17 '16 at 09:20

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

score 1 · Answer 3 · answered Jun 17 '16 at 09:44

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]>

How to list all columns in table with Sql trigger

3 Answers3