Can I CREATE TRIGGER in an rds DB?

Question

Its easy!

Open the RDS web console.

Open the “Parameter Groups” tab.

Create a new Parameter Group. On the dialog, select the MySQL family compatible to your MySQL database version, give it a name and confirm.

Select the just created Parameter Group and issue “Edit Parameters”.

Look for the parameter ‘log_bin_trust_function_creators’ and set its value to ’1′.

Save the changes.

Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.

Select the just created Parameter Group and enable “Apply Immediately”.

Click on “Continue” and confirm the changes.

Again, open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Modify”.

Dont forget: Open the “Instances” tab. Expand your MySQL instance and issue the “Instance Action” named “Reboot”.

Via - http://techtavern.wordpress.com/2013/06/17/mysql-triggers-and-amazon-rds/

No it is actually not impossible it just takes far too much extra work.

First off it seems to be impossible to apply Super Privileges to a default parameter group. So what I had to do was to create a new DB Parameter group either through the Console, or the CLI.

What I found was, the key is that the default region was not the region I was trying to use so I had to use a --region parameter to apply it to the group in the correct region where I was deploying my DB Instance

rds-create-db-parameter-group --db-parameter-group-name allow-triggers --description 'parameter group to allow triggers' --region your-region 

Next I had to create a DB Instance which used that parameter group. (Again through the console or CLI)

rds-create-db-instance

Then I had to modify the Parameter group to allow log_bin_trust_function_creators only accomplishable through the CLI

rds-modify-db-parameter-group --db-parameter-group-name yourgroupname --region yourRegion --parameters 'name=log_bin_trust_function_creators,value=true,method=immediate'

Lastly I had to modify the created DB Instance to allow triggers, also CLI only.

rds-modify-db-instance --db-instance-identifier your-db-instance-id --db-parameter-group-name allow-triggers --apply-immediately

In addition to the parameter group modification that others have already mentioned, there is a further challenge that arises when using a MySQL database dump (via mysqldump) to create triggers in an AWS RDS instance. You may get a message like this:

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

This happens because the dump contains "definer" entries with a username that's different than your RDS master username. One solution is to replace the definer username with your RDS master username. Another solution is not to use mysqldump to create your database.

See this blog post for more information:

http://www.percona.com/blog/2014/07/02/using-mysql-triggers-and-views-in-amazon-rds/

EDIT: It turns out Multi-AZ for MySQL uses "physical replication" and not logical replication, so this may not be correct. At least that's what their documentation says: https://aws.amazon.com/rds/details/multi-az/ - I have asked on their forums what this means, but did not get a reply. What's weird is that my RDS Multi-AZ instance claims it's a "master in a replication setup", even though I have no read replicas.

---

As the question has already been addressed, this is a comment more than an answer:

I'm surprised nobody takes into account why this feature is not available as a default. Amazon wouldn't disable it just to make people's lives harder.

In a master/slave replication it can be dangerous to use stored procedures and triggers that modify data (as in perform queries other than SELECT).

Please have a read below before disabling this restriction in a master/slave setup, which Amazon RDS is when you use Multi-AZ (and you should, for production at least).

http://dev.mysql.com/doc/refman/5.6/en/stored-programs-logging.html