Jump to content

MySQL triggers


Fukushousha
 Share

Recommended Posts

Hello all,My database is MySQL which unfortunately does not support check constraints or inheritance, both of which I wanted to implement. I kind of implemented some form of inheritance between tables by using foreign keys but I need to check them with check constraints, which are also not suported by MySQL :) Which brings me to this topic, triggers. I am trying to create a trigger which will check before inserting data on a table if the userName to be inserted in this table, is in the users table and has a userTypeID of 1. If yes I want it to insert the data in the table, if not to post some kind of SQL error.To be honest my SQL knowledge about triggers is limited and close to none. So here I go trying to create the trigger:CREATE TRIGGER usrTypeTRIG BEFORE INSERT ON progUsers BEGIN IF new.username = users.username AND users.usertypeID = '1' insertData(); ELSE displayError(); ENDIF ENDI don't know what code to put between the begin and END keywords. What I put in is pseudocode, can anyone please help me do it in mySQL? Assuming such a thing is at all possible. Also if triggers is not the way to do and there is a better way to do it in MySQL tell me please :)

Link to comment
Share on other sites

I know that no reply has been given up to now but I still seem to not understand how to do this. Can anyone help? Can anyone at least tell me how to,given a certain condition check in a trigger, stop an insert on a table from happening?

Link to comment
Share on other sites

First off, check the different storage engines in MySQL. InnoDB and MyISAM support different features, one of them supports things like constraints (I think it's InnoDB). For creating a trigger or stored procedure, the best thing to do is just read through the manual. It sounds like a cliche, but it's the place to start with things like this. Since I haven't set up a trigger in MySQL, if I were to help you then I would start by reading through the manual to see what it says about the syntax and things like that.http://dev.mysql.com/doc/refman/5.0/en/storage-engines.htmlhttp://dev.mysql.com/doc/refman/5.0/en/triggers.htmlhttp://dev.mysql.com/doc/refman/5.0/en/sto...procedures.html

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...