Learning MySQL - TRIGGERS
ฝัง
- เผยแพร่เมื่อ 10 มิ.ย. 2019
- This episode explores how you can create and use TRIGGERS with your MySQL tables as data protection for your tables.
A good purpose for one would be to add a last_updated timestamp field.
MySQL Course Playlist:
SQL file Code GIST: gist.github.com/prof3ssorSt3v...
MySQL Trigger reference: dev.mysql.com/doc/refman/5.7/...
The clarity of your voice and explanations make your tutorials enjoyable to watch. Which is something I never thought I'd say for SQL tutorials !
This video is really good. Thanks for making this. It helps just having someone walk through an example and I like that you explained the reasoning for each piece of the code that you wrote.
Have a good day
you clear the lessons for 10 minutes which i get difficult to undrstand when i listened to my teacher, thank you so much sir
One of the greatest tutorial video, super easy to understand. Give you a sub because of names in your table, the real man of cultures!
All your tutorials are very helpful, i always learn something new from your videos. Keep it up!
Great job! Very easy to understand your explanation. Thank you so much!
Thanks, Steve, I just realized I should have been creating triggers all this while to prevent inconsistent data in the tables.
You're an amazing teacher. Thanks for contributing
Thanks for sharing crips and crystal clear concepts.
Much appreciated, very clear and well paced lesson
Your voice is truly melodic! Thank you for your explanation!!!
just this is enough to the point I wanted it to be. Lemme watch others i.e. stored procedure as this was the first video to land on on a right teacher like you
I understood trigger now.
Thank you so much. 👍
Learning is easy with right teachers ☺️, I am glad to hear this lecture.
brief and simple explanation ,make it perfect thanks a lot :)
Thank you so much sir, got more information about the triggers in 10 minute
videos
Thank u so Much , definitely will recommend it To A friend
Thank you! Clear and simple to the point.
excelent tutorial.
Thanks a lot
Thanks pal, very useful tutorial.
Great job Steve 👍 love from India ,read this topic in my computer science graduation and still not understand it ,,,,like what a learned today,,,so thanks 🙏
job well done. Thank you
Thanks man. Awesome!!
Well explained..👏🏻
Thank you!
awesome man :-)
Thank you!!!
Thank you Sir
Thanks bro it woked
thank you sir
Thank you
If only the trigger problems were this easy in my college class. Mine are more like "Create a trigger named trg_char_hours that automatically updates the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values."
omg the aircrafts question, my class as well
This is really great! I got puzzled during the process when you set NEW.name = UPPER(NEW.name) as I thought the latter should be OLD. It makes sense when thinking again about it, but maybe it will help to clarify.
We are taking the NEW value that gets passed in and converting that NEW value to uppercase. We are not touching the OLD value. We don't care about it any more. We are going to be replacing it with the NEW value.
Quality tutorial man. Is it possible to interact with other databases in different servers to have the databases concurrently work?
If you are talking about multiple databases that are connected and sharing responsibility for the same data then that would be a cluster - dev.mysql.com/doc/mysql-cluster-excerpt/5.7/en/mysql-cluster-basics.html
If you are talking about different databases storing different data then you need to use other tools / write your own code to manage that concurrently.
@@SteveGriffith-Prof3ssorSt3v3 I needed clustering. Thank you for clarifying.
Thank you for such a clear and straightforward explanation!
What if I am inserting 500k rows into a table using an "INSERT INTO table SELECT * FROM....", and want the trigger to return the last auto increment id value (after the insertion has taken place) is there a way to build the trigger that way? So that it is not FOR EACH ROW but instead just for the last row inserted?
A trigger is on every operation. Maybe consider a stored procedure
Your videos have been providence for me! I have a question: Why is an error returned that a column doesn't exist when I try to run an "after insert" trigger while the column does exist in the database. The column is used as a filter in a "Where" clause within the trigger body. I use the same syntax not in a trigger body and it works.
WHERE clauses filter based on values in columns. They can't work if the column doesn't exist within the scope of the current query. The Trigger is like another query running on its own.
great explanation. One question tho. Is that possible to use a trigger with WebSockets to achieve real-time data access? eg. chat app or is there another way in addition to Graph QL subscriptions.
Triggers are only running within the database. They cannot trigger things to happen outside the database with your code. They can cause changes within the data, like setting a flag value in a table. Your code can check the database to see if the flag is set within the data and then decide to do something.
Ok. Thanks.
hey Steve, thank you so much for making these videos, they're very helpful! I have a question: I have a table which has a column called "expiration date"; is there a way to make a trigger that inserts values into another column when that expiration date is close to happening? thanks for your help
Triggers are tied to changes made to data in a table. If you add or delete or update data in a table, then that will cause your trigger to run. Triggers don't monitor values and compare the values against some other value to run some procedure.
If you want things to happen on a schedule, like checking once per day if the value in a column called "expiration date" has reach some threshold, then you need to do that outside the database. Cron tasks and similar things can run a server-side script which looks at the data and decides what to do.
I would probably create a VIEW of all the expiry dates and the row ids for each item and have that view sorted/indexed by the date. The VIEW will always reflect the latest data and is an easy thing to run queries against. The result of the query can be used to update whatever you want.
@@SteveGriffith-Prof3ssorSt3v3 thank you so much for your reply! I'll look into it; unfortunately I'm starting out with databases and such, do you happen to have any resources I may look into to get it done? Again, thank you for being such a great help!
@@camiloandreshernandez1234 This playlist is my only relational DB resource.
You might want to look into MySQL Scheduled Tasks. dev.mysql.com/doc/refman/8.0/en/events-overview.html
I don't have a video on them but they might help you.
@@SteveGriffith-Prof3ssorSt3v3 thank you so much Steve! I'll be sure to check it out :D
I'm having a trouble in implementing mysql triggers
Is it possible to create a trigger on a table that will delete a record from the table whenever something gets inserted into it? I have to make sure that there are between 80 and 100 records in a table but every hour a new item gets inserted into it by a script. I need to use the value of whatever gets inserted last so I thought to achieve this by defining a trigger that will delete the oldest item in the table whenever something new gets inserted. However I get an error "Can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger."
Thanks in advance
triggers are more of a row-level thing. Delete a row and then do something with the deleted value or insert a row and do something with the inserted value - like check if it is a valid value. A trigger can access both old and new data in its own table. A trigger can also affect other tables, but it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.
I would suggest creating a stored procedure that you can run from your server-side code each time you do an insert.
OR
Call the stored procedure from the Trigger. Here is a quick reference for doing this - www.mysqltutorial.org/mysql-triggers/mysql-call-stored-procedure-from-trigger/
OR
Create an Event that runs on a schedule - dev.mysql.com/doc/refman/5.7/en/create-event.html - like a scheduled task that checks the size of the table once an hour and
@@SteveGriffith-Prof3ssorSt3v3 thanks for the quick reply, I went with the event scheduler. Didnt know this existed, thanks alot!
When I try to use the github sql file to create the tables in Oracle SQL I keep getting this error 'identifiers may not start with any ASCII character other than
letters and numbers.' I think it may be due to all the back ticks you have in the file but I'm not 100% sure. Any help is appreciated.
Oracle, msaccess, SQL server, mysql, etc. they all have minor differences in what characters they use and slight variations in their version of SQL. Sometime * is a wildcard. Sometimes % is the wildcard. Sometimes you need to use double quotes and sometimes not. Backticks work for some but not others.
I need to create a trigger that sets/creates an automatic order (in a second table), whenever the stock of that product (in a 1st table) gets to zero. Any ideas, please? Ty!
Triggers only run on the table where they were created. They can't cause a change in another table.
However, if you call a stored procedure instead of just an UPDATE command, then you can use the trigger to set a value in a variable like in this example - dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html - and then, in the Stored Procedure, run your next SQL command too update the other table.
@@SteveGriffith-Prof3ssorSt3v3 Wow! Thank you so much! You're awesome!
Sir, one question, can we make a trigger run a process in Windows, or call a method in some DLL
Not from MySQL. No.
@@SteveGriffith-Prof3ssorSt3v3 Thanks sir for you quick reply, u have a fan here
I appreciate the video, but I was hoping for a more practical example. Something like if a new row is added to employee, then automatically add the next employee ID in the sequence.
Having an automatic new id is what setting your primary key column to AUTO_INCREMENT is for.
@@SteveGriffith-Prof3ssorSt3v3 Thank you! Keep making videos! I look forward to seeing more of them.
Hi..Is it possible to update a field of type timestamp on updation of a particular column?
Yes. This stackoverflow answer has a good code sample
stackoverflow.com/questions/22170758/mysql-timestamp-fields-created-modified
I need help making a trigger when the limit of 10 is passed in a table
Do a trigger on insert that calls a stored procedure. The stored procedure could do things like check the number of records in the table.
Here is a reference on combining triggers with stored procedures - www.mysqltutorial.org/mysql-triggers/mysql-call-stored-procedure-from-trigger/
Please can you help me, why does my trigger on employee table says error you can’t create triggers on system tables?? Is employee created by me on the mysql db a system table?
Did you create the employee table inside one of the system databases?
I got the following error, when I do call procedures.
SQL query:
CALL char_race('Fili', 12)
MySQL said: Documentation
#1305 - PROCEDURE movies.char_race does not exist
It says that you have not created the function or stored procedure called `char_race` in your database.
Is there a way to see stored triggers?
If you are using PhpMyAdmin, open a table and you should see a tab at the top called "triggers".
@@SteveGriffith-Prof3ssorSt3v3 Thanks :)
why are you using so many browsers?
I am a web developer. I have to be able to test what I build on multiple platforms.
would have liked, but it had 777likes and 7 dislikes ^^
I am in love with you . I love you Steve Griffith . I would like to be your friend .