MySQL- Triggers help please

SoldierExile

Hello, I need some help with triggers.

I need to create a trigger named DENY_DML that will not allow a delete, insert or update on the EMP table outside the hours 8am to 6pm Mon-Fri and anytime on weekends.

My code I know will be outlined like:

CREATE TRIGGER DENY_DML
  BEFORE                                  /* must be before so table is not altered */
    {[B][COLOR="Red"]INSERT|UPDATE|DELETE[/COLOR][/B]}                /* [B][COLOR="Red"]Needs to be all 3[/COLOR][/B] */
    ON EMP                               /* The table it will affect */
    FOR EACH ROW                          /* will affect every row */
BEGIN                                      /* my code that determines if insert, update, or delete will be allowed */
IF TIME =                               /* was thinking of using IF and ELSEIF statements but [COLOR="Red"]dont know how to stop the delete, insert 
                                                   or  update[/COLOR] */
END

That is my layout that i was going to use, and there are comments in there so you know what i was thinking, Dont tell me its possible but will give me headaches. ive been told that and no one so far will help me not even google *sigh*

Mostly in a nut shell, i need to use INSERT UPDATE DELETE in one trigger, and need to prevent anyone from inserting, updating, deleting on that table in a certain time frame.

mrcheesypants

Haven't tried this personally, but according to the comments at http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html (scroll down near the end to see a code example) you can set the primary key of an item to an already existing item thus denying the insert. Sounds kinda hackish to me, but I'm not sure if there is a better way. Keep in mind that my SQL is pretty weak.

Ganluan

First off, it looks like they each need to be done in separate triggers: http://stackoverflow.com/questions/1845329/mysql-triggers-can-a-trigger-stop-all-dml

Second, is there a reason you can't just run a job at the specified times that will revoke those privileges on the table?

vonPoonBurGer

I honestly don't understand why people still use MySQL. It finally has triggers now. Except you can't throw your own exceptions from within them. And you can't execute two triggers in response to an event. And you can't specify one trigger to fire on insert OR update OR delete. If you need a lightweight open source DB, use SQLite. If you need a feature-rich open source DB, use PostgreSQL. MySQL is usually too much DB for the first need and not enough for the second.

Ganluan's solution is probably your best bet in the short term because it completely obviates the need to rely on MySQL's rudimentary trigger support.

If it has to be in triggers, what mrcheesypants linked will work, but it requires that you be able to reliably create an exception (e.g. purposefully violate a primary key constraint) so that both the trigger and the action that caused it to fire get rolled back. Note that this could be a terrible solution for a busy database, because you might be spending a lot of I/O every time the trigger gets fired (some for the insert/update/delete, then some more for the rollback).

SoldierExile

Lol, you are funny, why dont i use SQL lite... or try another language for DB.... because im taking computer science classes in college, and apparently im to stupid to figure out how to do what i asked help on.


but today, my teacher updated our assignment, I can use multiple triggers. not just one trigger to do this.

and as i understand what im supposed to do, i have to do it with a trigger. if you want to look at what assignment i was given

1. Create a trigger called UPPERCASE_ENAME that insures
the ename of the emp table is always uppercase for
inserts and updates.


2. Create a trigger named DENY_DML that will not allow a
delete, insert or update on the emp table outside the
hours 8am to 6pm Mon-Fri and anytime on weekends.



Note i can now create more then one trigger for each job. But i still dont know how to stop a command from being issued at certain times.

vonPoonBurGer

My advice was presuming that this was a production DB for some kind of personal project or corporate application. I didn't mean it to imply that you are somehow less of a person for selecting MySQL, I assumed (semi-incorrectly) that this was a DB you inherited and now had to extend/support. That's often the case with DB help threads in H&A. In a way, it's also the case here. I doubt you get to choose which database you use for coursework.

To be perfectly honest though, if I'd known this was to help you do your homework I wouldn't have replied in the first place.

SoldierExile

vonPoonBurGer wrote: »

My advice was presuming that this was a production DB for some kind of personal project or corporate application. I didn't mean it to imply that you are somehow less of a person for selecting MySQL, I assumed (semi-incorrectly) that this was a DB you inherited and now had to extend/support. That's often the case with DB help threads in H&A. In a way, it's also the case here. I doubt you get to choose which database you use for coursework.

To be perfectly honest though, if I'd known this was to help you do your homework I wouldn't have replied in the first place.

may be for homework, but i didnt ask for the code either, i asked for help, theirs a difference. but its all good no thanks to you. someone else gave me help. and no they didnt give me any code for it they gave me a general idea of what i would do.


only, and last time i ask for 'help' from here.

Lewisham

SoldierExile wrote: »

only, and last time i ask for 'help' from here.

Good. I report'd you after you were a huge jerk to vonPoonBurGer, but I'll take a self-ban too.