ฝัง
- เผยแพร่เมื่อ 12 ก.ค. 2024
- #MySQL #tutorial #course
00:00:00 intro
00:00:33 setup part 1
00:02:01 BEFORE UPDATE
00:05:45 BEFORE INSERT
00:08:14 setup part 2
00:10:45 AFTER DELETE
00:12:37 AFTER INSERT
00:14:34 AFTER UPDATE
00:16:25 conclusion
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";
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.
Such underated channel
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.
Now I have accomplished the whole playlist of MYSQL very happy and learned so much from the playlist
Good job! Please keep quality of your videos.
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.
Thanks you for your informative video with your efforts.
do stored procedures next please. Love the videos, keep up the good work!!!
best video explains the concept of trigger
Great video series! Your presentation was easily understood and easy to follow Thank you!
You were God sent man. This tutorial really helped me out A LOT! Thanks so much!
Thank you very much for this 5 Star course!!! Learned a lot
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
Very well explained, Thanks
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.
amazing tutorial
Amazing video. Great explanation!
so hard to learn, but your video take less time to know, thank you for this pure video, may god bless you bro
Wow! Thanks a lot ❤you have explained it in its best way which I have never seen before.
great job bro, you are sharp as blade
Make a tutorial for PostgreSQL too. That would be extremely helpful.
Thanks of the great tutorials 👍👍
nice video bro....
Cool vids dude.
godlike study material
thx for lesson bro, as always !
Currently in a database class. I hated Java but it’s so much easier doing SQL your examples and explanations are great
Thanks
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!
Hey. Nice
Great Job Bro 🖤
The most surprising thing to learn on this video is that plankton is fr named sheldon kkkjkjk
Please upload nodejs your content is priceless
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
How would you decide between applying triggers in these scenarios vs doing so programmatically in backend code as a software engineer?
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
nice
really?
yeah
wow
Just finished all the videos. Thank you for the clear explanation sir
Hey man could you please tell me how did you managed to make your scripting background black?
12:13 Poor Plankton xD
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 😅
W course
can you do a video for random numbers in columns please.
please do a springboot tutorial
Hi bro, are you going to make node js videos?
Please do functions
9:48 Turbotax: Don't do your taxes.(not sponsored)
12:12 Poor Plankton got fired and reemployed twice
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
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
Is this sql series completed?
What is difference between before and after
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.
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 😀🙌🏼
Bullying plankton😂😂😂
poor Plankton 🤣
Most of my issues come from triggers...
PLANKTON NOOOOOOOOOO
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
i feel sorry for plankton
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?
Absolutely Brilliant Fucking Video! Thanks Bro!!! ❤
I am facing unknown column hourly-pay in field list error after create update trigger. Please tell me solution