How to Copy Column Data with Trigger in INSERT statement in MySQL – DocuWare

This request came from a DocuWare application where a user wanted to have DocuWare internal fields as part of the file cabinet fields. In DocuWare you cannot have the internal file cabinet system fields as part of regular file cabinet fields. The solution is to create a MySQL trigger that copies the system field values while the data is been inserted into a file cabinet table.

This does not need to be specific for DocuWare application it could also be implemented with other applications or other tables. It is a simple MySQL trigger to copy the data from one column to another.

STOP

STOP

NOTE: THIS IS A SQL STATEMENT AND IF YOU MISS-CONFIGURE OR TYPE INCORRECTLY, IT COULD CAUSE SERIOUS DAMAGE TO YOUR DATABASE, TAKING A FULL BACK UP OF THE DATABASE MAY PREVENT THESE DAMAGES. WE (DOCUWARE, BLOGGER, THIS SITE) ARE NOT RESPONSIBLE FOR ANY DAMAGES. DO IT AT YOUR OWN RISK.

Here are the steps:

1.       Create a field(s) in MySQL table. In my case I have added three additional fields to my DocuWare file cabinet.

Copy Column Data MySQL

Copy Column Data MySQL

2.       Here is the MySQL Script to create that trigger.


/* Move data to new column */
DELIMITER //
CREATE TRIGGER dwdata2.t_i_CopyDWSYSFields_to_NewColumns BEFORE INSERT ON dwdata2.orders_fc
FOR EACH ROW
BEGIN
SET NEW.DOCUMENTID = NEW.DWDOCID;
SET NEW.DOC__PAGE_COUNT_ = NEW.DWPAGECOUNT;
SET NEW.DISK_NUMBER = NEW.DWDISKNO;
END;
//

Explanation:
1.       DELIMITER // … start the code

2.       CREATE TRIGGER … creates a trigger with the given name

3.       BEFORE INSERT ON … specifies the vent

4.       FOR EACH ROW … statement that goes with most triggers

5.       BEGIN …  the actual data manipulation starts

6.       END; … ends the code bock

7.       // … end the MySQL delimiter code


About onlinejt

Blogger

, ,

One Response to How to Copy Column Data with Trigger in INSERT statement in MySQL – DocuWare

  1. Juan Tenemaza 11/07/2010 at 10:24 pm #

    When migrating or moving data, in this case copying data from one column to another, you have to make sure that you create fields with the same data type. In the above SQL script I am moving data from a numeric field to a text field, which is OK for now. But if I was copying from text to numeric, then it will be an issue. I have to find some type of data conversion first before it is assigned to a new field.

Leave a Reply

*