We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Self-updating table in InnoDB

Medium Priority
493 Views
Last Modified: 2012-05-11
Hopefully I can explain this as well as needed.

I am currently designing the database that will run an ERP I am developing. The ERP primarily internal with external access via the internet as required. I am using InnoDB tables in MySQL 5.x.

Part of the ERP is user permissions. I am planning on having two views for updating and assigning permissions. One 'broad' and one 'granular'. In the broad view I would like to assign 'x_create' to a user, which will imply 'x_delete'. In the granular view I will be able to select 'x_delete', 'x_create', 'x_modify' etc... separately, giving more control.

To minimize the amount of code required to check permissions, I was hoping there is a way within the database itself of assigning conditional statements.

Example:

If table.x_create = 1 then table.x_delete = 1 (this will fire ONLY when updating table.x_create)

It should not fire when table.x_create is set to 0 and consequently should not set x_delete to 0. This preserves the granular control (ie- removing permission to create while maintaining permission to delete)

Does such a thing exist, and can you link to a reference or provide an example of how to set it up? I can set this up in PHP, but it requires either:

1. Additional checks if more than one permission implies more than one other permission
2. Checks at the time of assigning permissions to update other permissions depending on the broad/granular view used.
Comment
Watch Question

Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
I did consider triggers before asking, but I've never really found a whole lot of documentation for it. Perhaps something like the following:

(Table: user_permissions)
(Adding a field for 'broad' which I'll set to field_name when using the broad form)

CREATE TRIGGER user_permission_verify AFTER UPDATE on user_permissions
   FOR EACH ROW
   BEGIN
      IF NEW.broad = 'user_create' THEN
         IF NEW.user_create = 1 THEN
            SET NEW.user_modify = 1; // user_create implies ability to user_modify
            SET NEW.user_delete = 1; // user_create implies ability to user_delete
            SET NEW.broad = NULL; // Reset the granular update flag.
         END IF;
      END IF;
   END;

Open in new window


I haven't tested this as currently I have no data in my tables. I don't know if the = operator should be = or == yet.

In theory, after an update, if user_permissions.broad = user_create it'll check if user_permissions.user_create == 1 then change user_permissions.user_modify and user_permissions.user_delete to 1 also.

Author

Commented:
Slight tweak to my code:

It has to be BEFORE, not AFTER when using SET apparently. Also, comments are # not //.

I'll be able to set this up to do what I wanted.

Thanks again.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.