CODEWITHSUNDEEP
mysqlsqltriggers
Айдън Бейтулов
Айдън Бейтулов

Reputation: 75

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

Upvotes: 1

Views: 3335

Answers (3)

angel.bonev
angel.bonev

Reputation: 2232

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

Upvotes: 2

Bernd Buffen
Bernd Buffen

Reputation: 15057

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

Upvotes: 1

Shadow
Shadow

Reputation: 34231

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

Upvotes: 1

Related Questions