[HOW TO] MySQL Trigger

8 posts by 2 authors in: Forums > CMS Builder
Last Post: August 18, 2010   (RSS)

By richb - August 16, 2010

Do you see any issues with creating a MySQL Insert Trigger when a new row is created? I have a field called "SKU" and I want this to be a random #. Instead of user entering (and having to know a unique#) I was going to create a MySQL trigger to do this upon insert. Any concerns?

Re: [richb] [HOW TO] MySQL Trigger

By Chris - August 16, 2010 - edited: August 16, 2010

Hi richb,

The simplest solution would be to use the existing "num" field. You could add some silly-huge number to this to make your SKUs all look uniform.

I think that using a trigger might not work because of how CMS builder updates records right after inserting them. I'm not entirely sure if this would work or not, but the good news is that it's super easy to do with a tiny custom plugin (see attached.)

I hope this helps! Please let me know if you have any questions.
All the best,
Chris
Attachments:

custom_generate_sku.php 1K

Re: [chris] [HOW TO] MySQL Trigger

By richb - August 16, 2010

I have a table called "Products" and in the table there is a field called "SKU". When a new product is entered I want to generate the unique sku. How would this code look?

Re: [richb] [HOW TO] MySQL Trigger

By Chris - August 17, 2010

Hi richb,

The plugin I attached will generate "unique" sku's (see the documentation for PHP's uniqid function) for new records on any table that has a field called 'sku'. If your field is actually called 'SKU' instead of 'sku', you will need to change 'sku' to 'SKU' in the plugin.

If you have other tables with 'sku' fields which you don't want auto-generated, let me know and I'll customize the plugin to only work for your Products table.

To install the plugin, save it in your /cmsAdmin/plugins/ directory, log into CMS Builder, go to Admin -> Plugins, find it there (it's called "Custom Generate SKU") and activate it.

Please let me know if you have any questions.
All the best,
Chris

Re: [chris] [HOW TO] MySQL Trigger

By richb - August 17, 2010

Yes, I would like a specific example of how I would generate a unique sku. Below is the specifics.

Table: Products
Field: prodname [textfield]
Field: sku [textfield] (plugin to update this field on new rec)
Field: qty [textfield]

Thank you
richb.

Re: [richb] [HOW TO] MySQL Trigger

By Chris - August 17, 2010 - edited: August 17, 2010

Hi richb,

I've modified the plugin to only function on a table called 'Products' and have attached it.
All the best,
Chris
Attachments:

custom_generate_sku2.php 1K

Re: [chris] [HOW TO] MySQL Trigger

By richb - August 17, 2010

This work great. I added brand and category info to clearly define the "sku". Is there more documentation on creating plugins? For example, on the addAction function the first parameter 'record_presave', what other parameters are there.

Thanks again this was perfect.