I am trying to add a trigger statement into my program in eclipse. The end goal is to not use mysql to alter any information in my code. I have a successful trigger (i think) in mysql, at least it throws no errors, but when I paste it into eclipse, I get an error. I am trying to limit the number of videos posted per user per day, so a user may not post more than 5 videos per day.
This is my statement straight from eclipse. And this is my error
The END IF/END worked fine in mysql workbench, so I'm not sure what to change here.
I don't know why you would want to do that. Typically what people do is to use the database to create tables and views and triggers, basically anything which is only going to be done once in the lifetime of the project. Then they write Java code to work with those database structures and do things which are going to be done every day, or every second. So I don't see much point in trying to get Java to add a trigger. (I'm also not sure I would implement a business rule like that as a trigger, but that's a separate issue.)
Having said that... I'm pretty certain you didn't just copy that trigger code from the MySQL workbench into Eclipse. It looks like you've put quotes and + signs and so on into it so that it's valid Java code. But probably you've preserved the number of lines. In which case it would have looked like
in the workbench. When you do that, the workbench assumes that there's a space between '45000' and END, I think, but your Java code doesn't put a space in there. So that's my guess as to why you have an SQL issue.
Okay... why wouldn't I use a trigger to implement that business rule?
Because it's not necessarily a permanent feature. A year from now you might decide that a customer can post up to 20 videos per day. Then you've got to go in and change that trigger. And then you might decide that premium customers can post up to 100 videos per day but regular customers can still only post 20. Gotta change the trigger again.
I would implement the rule in Java because it's likely to be changed in the future. You're always doing changes to the Java code to fix bugs and add improvements and so on, so changing business rules fits in with that process nicely.
I took the OP as saying they were trying to do exactly that, ie remove the existing trigger from the database and putting the logic for it into the Java code.
The problem is that's not what's happened.
You can't just take the trigger code (or in this case the trigger CREATion code) and treat it like some workable SQL.
You're going to need to translate it into a Java/JDBC environment.
As a quick guess I would say do the SELECT statement then check the count returned. If too much then handle it however you want.
MySQL Workbench is a GUI for interacting with a MySQL database server(s). Eclipse is an Intelligent Development Environment, which can be used to interact with a database server (using an Eclipse database plug-in) or to develop a Java application that uses JDBC (usually) to interact with a database (or databases).
In either case, a trigger is defined in DDL, which is the vendor's proprietary Data Definition Language. Meaning that it's not standardized like SQL, but it does have SQL-like syntax. DDL not only defines and maintains triggers, but of course, also tables, views and often even entire databases.
I personally prefer to have my DDL operations done externally via something like MySQL Workbench, the mysql command-line client application, or the Eclipse database plugin. DDL is normally done infrequently (sometimes only once), and it's not worth the grief it takes to package it all up and run it from a Java application. But that's not always possible or convenient, as, for example, Android applications, where there's really no built-in SQLite database editor app, so the user application has to define the database itself.
A trigger is a good way to enforce business rules in a way that doesn't require application code changes when the rules change. In this particular case, attempting to add a 5th video would cause a SQL INSERT to throw a SQLException, and the only tricky part is determining that it's a trigger exception rather than one of the other myriad types of SQL exceptions (and, alas, there's absolutely no standard for that sort of stuff - every database product is different). A trigger is also for enforcing rules in a single control point in the event that multiple applications are doing this sort of thing.
Although if you want to be totally safe, don't check for count EQUAL to 5, check for count GREATER OR EQUAL to 5. That ensures that if somehow a 5th video had been defined, that the user wouldn't then be able to add an infinity of extra videos. The trigger, after all, only detects new inserts, not how many items might have been defined before the trigger was set in place.
"privilege" comes from the Latin words for "private" and "law" (legal) and dates to feudal times. To "claim privilege" meant that you were above the laws that applied to the common people.
Acetylsalicylic acid is aspirin. This could be handy too:
Devious Experiments for a Truly Passive Greenhouse!