The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
MySQL- Triggers help please
SoldierExile
Registered User new member
Registered User new member
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:
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.
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.
SoldierExile on
0
Posts
Second, is there a reason you can't just run a job at the specified times that will revoke those privileges on the table?
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).
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.
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.
Good. I report'd you after you were a huge jerk to vonPoonBurGer, but I'll take a self-ban too.