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.
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
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 😅
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)
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 😀🙌🏼
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.
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.
