We post new blogs every month. If you would like to learn more about a specific topic please contact us and let us know!

Latest Blogs

Share this

513-766-9111

312 Walnut St #1600, Cincinnati, OH 45202

contact@datadesignsystems.com

 

Data Design Systemscode snippets How to setup AWS RDS triggers
aws rds triggers

How to setup AWS RDS triggers

AWS RDS triggers setup

When I first started working with AWS RDS triggers there was a slight learning curve as to how the Relational Database Service (RDS) works. Although the initial setup is straight forward, I ran into an issue when it came to creating triggers.  I would get the following error.

ERROR 1227 (42000) at line 875: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

It took me awhile but I figured out the solution was to change log_bin_trust_function_creators from 0 to 1. Sounds simple right? So the next question is how do you do it?  In the video below you will see the set by step process to do this…In addition I have also outlined the process below.

How to change  log_bin_trust_function_creators  step by step

  1. Create a new instance of MySQL RDS (if you have a current instance this will work too.)
  2. Click on Create Parameter (You will see your default parameter group. You can not edit this group, you must make a new parameter group to edit.)
  3. Fill in the required information then click create.
  4. Once you see your parameter group in the parameter group table check the box next to it.
  5. Click on the parameter group actions dropdown menu.
  6. Click edit.
  7. Search in the filter input for log_bin_trust_function_creators 
  8. Change the setting from 0 to 1
  9. Then click Save Changes
  10. Now we need to assign the parameter group to the MySQL DB instance.
  11. Find your instances
  12. Check the radio button next to the instance you wish to assign the parameter group to
  13. Click on the instance actions drop down menu
  14. click modify
  15. Find the DB parameter group drop down
  16. Select the newly created triggers parameter group.
  17. Click continue
  18. Then save the DB changes
  19. Once you make the update you will need to reboot the DB instance before the triggers can be added.
  20. Go ahead and reboot your DB instance.
  21. Now you can create triggers in your MySQL instance.

AWS RDS Triggers

INSERT Trigger

The word “created” refers to the datetime column that will be updated automatically once a row is inserted.

create trigger trigger_name before insert
on table_name
for each row
set new.created = now();

UPDATE Trigger

The word “updated” refers to the datetime column that will be updated automatically once a row is updated.

create trigger trigger_name before update
on table_name
for each row
set new.updated = now();

If you need assistance with RDS setup or have an AWS question feel free to contact us at 513.766.9111 or email us at contact@datadesignsystems.com . We can help solve any database issue.

No Comments

Leave a reply