CREATE TRIGGER before_hourly_pay_update BEFORE UPDATE ON employees FOR EACH ROW SET NEW.salary = (NEW.hourly_pay * 2080); CREATE TRIGGER before_hourly_pay_insert BEFORE INSERT ON employees FOR EACH ROW SET NEW.salary = (NEW.hourly_pay * 2080); CREATE TRIGGER after_salary_delete AFTER DELETE ON employees FOR EACH ROW UPDATE expenses SET expense_total = expense_total - OLD.salary WHERE expense_name = "salaries"; CREATE TRIGGER after_salary_insert AFTER INSERT ON employees FOR EACH ROW UPDATE expenses SET expense_total = expense_total + NEW.salary WHERE expense_name = "salaries"; CREATE TRIGGER after_salary_update AFTER UPDATE ON employees FOR EACH ROW UPDATE expenses SET expense_total = expense_total + (NEW.salary - OLD.salary) WHERE expense_name = "salaries";
@@AMANMUJAWAR-sq6qv Hi, the error is due the Safe Update Mode in MySql, in order to disable it you have to execute the following SET SQL_SAFE_UPDATES = 0; then you can make the updates, and after that, re-enable it in order to avoid any unwanted changes with the following command SET SQL_SAFE_UPDATES = 1;
words cannot describe how grateful i am for discovering your channel a while ago. I first found out the java course, then went to python and then on my database subject in university. ALL of your videos found it useful and simple as hell. Learned a lot of stuff and you make it look easier man, i appreciate you :> thank you. Really... thank you.
I very rarely comment, but man, what a great video. Not unnecessarily long, but still with plenty of content to thoroughly understand the concept. Liked and subscribed, and I'm looking forward to exploring more of this channel. Thank you.
I watched all the videos on this list and learned a lot. I like the way you give examples. Your content is excellent. Please continue, thank you very much Bro Code.
For those who confuse about the last one why the expense total is 356200 is because expanse total=current expanse total which is 254280 +((new salary for plankton which is 208000 or 100*2080 )-(old salary for plankton which is 106.080)) so the result is 356200. How do i know the 100*2800? Okay the answer is he already make before update trigger so it means that when he run the update trigger at the end the of the video he run two trigger which is before update trigger and after update trigger. 100 is the number he set when he run the update query. I hope you guys understand what i mean 😅
Hey brocode, ive recently finished all non gui vids of your in java, and taking a break before hopping in your introduction to swing. Your C# also made me fall in love with c#, and making it the first language im comfortable using. That being said, would you ever make a ruby series? I had learnt ruby back in 8th grade, and now im the 11th grade, having forgotten mostly everything about it, and i would really like an oppurtunity to brush up on my ruby knowledge, especially since nobody teaches like you do. Have a great day man
And that marks the end, hell yea. Honestly just wish functions were touched on like triggers and stored procedures. We got to see some of the built in functions, but never got to go over a user defined function. Nonetheless a really nice playlist for getting down the basics and syntax of mysql and sql respectively. Thank you bro
My opinion is to let a database just be a data store and program all these functions in your chosen programming language. Trying to debug and maintain these SQL functions would be a nightmare if you start creating lots of them!
4:51 why their are keywords like before and after.....so when we are dealing with another table that is when we use AFTER keyword ????simply if we are changing hourly_pay, it should immediately reflect into annual_salary? Plz reply with the reasons for Before and After....
It is really self explanatory, you asking yourself WHEN trigger has to activate. Thats it. In calculating salary case he chose to activate this trigger BEFORE UPDATING anything on table of employess. This can make you question why, so lets put easier example. Lets say, that column salaries has to be NOT NULL, therefore whenever you would INSERT new records, you coulnt put NULL values in sql statements, but if you create a trigger that will activate BEFORE INSERT / UPDATE on that table, salary value will be calculated with a proper NOT NULL value, so you wont have any errors even if you want to put null value in salary column (ofcourse assuming that hourly_pay column is a proper number) In case of calculating expenses, he wanted to calculate expenses anytime data was added or changed in employe table ( so AFTER a data was changed to use that new data to calculate new value), so he created a trigger for expenses table that will activate AFTER INSERT / UPDATE ON employee table. There's no point in calculating expenses before since data are different after you changed them.
@@TheAnimeSounds Thanks mate....I live in India. I am looking for a remote job, I am applying via linkedin to the US companies.....any suggestions for me???? I know SQL, Power BI and Python(Numpy, Pandas, Seaborn, Scikit)
I got a general knowledge on triggers but could not quite follow what's the difference between after and before thing. Like after insert and before insert. I did not comprehend the logic lies behind it. Can you explain like telling it to a layman.
I got confused by that too. I did a bit of googling and I think I get the gist of it. When it's in the same table, we use "before". If it involves data from another table, it's "after". But I may be wrong and this could just be the blind leading the blind lol.
hi man thank you for this job i just wondering in the last example if the user enter less hourly_pay it couldnt work because the new salary is less than the old salary however thank you man
I don’t get it. How can the salary column recalculate based on the hourly pay BEFORE the hourly pay has been updated? Surely the hourly pay update needs to happen prior to the salary being recalculated? Can anyone help me to wrap my head around that?
Here is my understanding Whenever you update hourly pay, before the update happens the trigger will have both old and the new values for all the columns in your table. So using the 'NEW' keyword for the salary you will get the new salary that is going to be updated, using that you are calculating the hourly pay and updating them simultaneously. Hope this helps 😀🙌🏼
with this video, you know already how to create triggers, what you dont know i assume is how to take first 4 chars from a text , just check build in methods, for example left() or right() and create your trigger accordingly to your needs by following this video.
CREATE TRIGGER before_hourly_pay_update
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);
CREATE TRIGGER before_hourly_pay_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = (NEW.hourly_pay * 2080);
CREATE TRIGGER after_salary_delete
AFTER DELETE ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total - OLD.salary
WHERE expense_name = "salaries";
CREATE TRIGGER after_salary_insert
AFTER INSERT ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total + NEW.salary
WHERE expense_name = "salaries";
CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
UPDATE expenses
SET expense_total = expense_total + (NEW.salary - OLD.salary)
WHERE expense_name = "salaries";
Bro Code Sir,
I have dought at 1:51
I am getting the error for not using the where clause after set command
Can you please let me know😢
@@AMANMUJAWAR-sq6qv
Hi,
the error is due the Safe Update Mode in MySql, in order to disable it you have to execute the following
SET SQL_SAFE_UPDATES = 0;
then you can make the updates, and after that, re-enable it in order to avoid any unwanted changes with the following command
SET SQL_SAFE_UPDATES = 1;
justice for Plankton ,he did nothing wrong
Maybe just consider he had the wrong skin color and DEI require certain "adjustments" to the employee tables.
😂😂 elon musk's mindset
words cannot describe how grateful i am for discovering your channel a while ago.
I first found out the java course, then went to python and then on my database subject in university. ALL of your videos found it useful and simple as hell.
Learned a lot of stuff and you make it look easier man, i appreciate you :>
thank you. Really... thank you.
Such underated channel
Now I have accomplished the whole playlist of MYSQL very happy and learned so much from the playlist
I very rarely comment, but man, what a great video. Not unnecessarily long, but still with plenty of content to thoroughly understand the concept. Liked and subscribed, and I'm looking forward to exploring more of this channel. Thank you.
so hard to learn, but your video take less time to know, thank you for this pure video, may god bless you bro
I watched all the videos on this list and learned a lot. I like the way you give examples. Your content is excellent. Please continue, thank you very much Bro Code.
Thank you very much for this 5 Star course!!! Learned a lot ❤
do stored procedures next please. Love the videos, keep up the good work!!!
You were God sent man. This tutorial really helped me out A LOT! Thanks so much!
Just finished all the videos. Thank you for the clear explanation sir
best video explains the concept of trigger
For those who confuse about the last one why the expense total is 356200 is because expanse total=current expanse total which is 254280 +((new salary for plankton which is 208000 or 100*2080 )-(old salary for plankton which is 106.080)) so the result is 356200. How do i know the 100*2800? Okay the answer is he already make before update trigger so it means that when he run the update trigger at the end the of the video he run two trigger which is before update trigger and after update trigger. 100 is the number he set when he run the update query. I hope you guys understand what i mean 😅
Thank you very much for this 5 Star course!!! Learned a lot
Wow! Thanks a lot ❤you have explained it in its best way which I have never seen before.
Thanks you for your informative video with your efforts.
Hey brocode, ive recently finished all non gui vids of your in java, and taking a break before hopping in your introduction to swing. Your C# also made me fall in love with c#, and making it the first language im comfortable using. That being said, would you ever make a ruby series? I had learnt ruby back in 8th grade, and now im the 11th grade, having forgotten mostly everything about it, and i would really like an oppurtunity to brush up on my ruby knowledge, especially since nobody teaches like you do. Have a great day man
Currently in a database class. I hated Java but it’s so much easier doing SQL your examples and explanations are great
Make a tutorial for PostgreSQL too. That would be extremely helpful.
The many examples were definitely helpful.
godlike study material
amazing tutorial
And that marks the end, hell yea. Honestly just wish functions were touched on like triggers and stored procedures. We got to see some of the built in functions, but never got to go over a user defined function. Nonetheless a really nice playlist for getting down the basics and syntax of mysql and sql respectively. Thank you bro
My opinion is to let a database just be a data store and program all these functions in your chosen programming language. Trying to debug and maintain these SQL functions would be a nightmare if you start creating lots of them!
Very well explained, Thanks
Great video series! Your presentation was easily understood and easy to follow Thank you!
goated youtuber🍺
Thanks of the great tutorials 👍👍
Good job! Please keep quality of your videos.
One last example then I promise we are done.
I mean ... it's not like you are holding me against my will bro. I kinda wanna learn this :D
bro thanks for this series it's really useful
nice video bro....
Amazing video. Great explanation!
Not working with Popsql and the reason was because you need to use BEGIN and END when you write a trigger.
Please upload nodejs your content is priceless
4:51 why their are keywords like before and after.....so when we are dealing with another table that is when we use AFTER keyword ????simply if we are changing hourly_pay, it should immediately reflect into annual_salary? Plz reply with the reasons for Before and After....
It is really self explanatory, you asking yourself WHEN trigger has to activate. Thats it.
In calculating salary case he chose to activate this trigger BEFORE UPDATING anything on table of employess. This can make you question why, so lets put easier example.
Lets say, that column salaries has to be NOT NULL, therefore whenever you would INSERT new records, you coulnt put NULL values in sql statements, but if you create a trigger that will activate BEFORE INSERT / UPDATE on that table, salary value will be calculated with a proper NOT NULL value, so you wont have any errors even if you want to put null value in salary column (ofcourse assuming that hourly_pay column is a proper number)
In case of calculating expenses, he wanted to calculate expenses anytime data was added or changed in employe table ( so AFTER a data was changed to use that new data to calculate new value), so he created a trigger for expenses table that will activate AFTER INSERT / UPDATE ON employee table. There's no point in calculating expenses before since data are different after you changed them.
@@TheAnimeSounds Thanks mate....I live in India. I am looking for a remote job, I am applying via linkedin to the US companies.....any suggestions for me???? I know SQL, Power BI and Python(Numpy, Pandas, Seaborn, Scikit)
@@akshaypatil8155 Im just starting to learn myself soooo :) unfortunetly no :P
nice
really?
yeah
How would you decide between applying triggers in these scenarios vs doing so programmatically in backend code as a software engineer?
Great Job Bro 🖤
great job bro, you are sharp as blade
thx for lesson bro, as always !
I got a general knowledge on triggers but could not quite follow what's the difference between after and before thing. Like after insert and before insert. I did not comprehend the logic lies behind it. Can you explain like telling it to a layman.
I got confused by that too. I did a bit of googling and I think I get the gist of it. When it's in the same table, we use "before". If it involves data from another table, it's "after". But I may be wrong and this could just be the blind leading the blind lol.
@@walk4754 :) thanks anyway
Hey. Nice
what is the difference between before insert and after insert?
9:48 Turbotax: Don't do your taxes.(not sponsored)
12:12 Poor Plankton got fired and reemployed twice
Cool vids dude.
Thanks
hi man thank you for this job i just wondering in the last example if the user enter less hourly_pay it couldnt work because the new salary is less than the old salary however thank you man
I don’t get it. How can the salary column recalculate based on the hourly pay BEFORE the hourly pay has been updated? Surely the hourly pay update needs to happen prior to the salary being recalculated? Can anyone help me to wrap my head around that?
Here is my understanding
Whenever you update hourly pay, before the update happens the trigger will have both old and the new values for all the columns in your table.
So using the 'NEW' keyword for the salary you will get the new salary that is going to be updated, using that you are calculating the hourly pay and updating them simultaneously.
Hope this helps 😀🙌🏼
W course
can you do a video for random numbers in columns please.
Please do functions
So this will work in the background?
Even if a user doesn't have the database open?
Hey man could you please tell me how did you managed to make your scripting background black?
What is difference between before and after
Is this the last video of this series ?
I also would like to know that.
@@zorrofit5880Me too. But he wrote he's not sure himself for how long series will go
please do a springboot tutorial
The most surprising thing to learn on this video is that plankton is fr named sheldon kkkjkjk
Hi bro, are you going to make node js videos?
Is this sql series completed?
wow
Most of my issues come from triggers...
Can you please show how to create trigger for username by taking first 4 char from first name and last name please
with this video, you know already how to create triggers, what you dont know i assume is how to take first 4 chars from a text , just check build in methods, for example left() or right() and create your trigger accordingly to your needs by following this video.
12:13 Poor Plankton xD
Hi bro, will you make a single video of all the SQL videos?
Most likely.
I'm not sure how long I'm going to continue this series tho.
@@BroCodez Why Bro?
that's so hardddddddd
Love❤ed this channel. Thanks for the videos bro. Can u also upload for Post GRE SQL. Subscribed channel
i feel sorry for plankton
PLANKTON NOOOOOOOOOO
Bullying plankton😂😂😂
poor Plankton 🤣
I am facing unknown column hourly-pay in field list error after create update trigger. Please tell me solution
Absolutely Brilliant Fucking Video! Thanks Bro!!! ❤