Hi. In this lesson, let's talk about database triggers. What is a database trigger? And why do we need it? A database trigger is a PL SQL block that is stored in the database and it is executed on an event related to a table. For example, you might want to execute some code whenever you are inserting a new row into the table.

The code is used to validate the insertion or it might be used to perform a cascading operation. I mean, whenever you insert a row into one table, you might want to update another table at the same time. Or you might want to insert a row into another table which is related to the table you are inserting into. So whatever might be the case You can use a trigger to perform such operations. And the most important application of trigger is integrity of data. We have a lot of business rules, and we can't implement all business rules using integrity constraints like primary key references, not null and so on.

So, a trigger is our answer. A trigger is what we use to implement complex business rules. And as you can see, that is one of the major applications of the trigger. A trigger can also be used to keep track of the changes we are making. For example, I want to know when I'm changing the salary of an employee, whenever I change salary of any employee, I would like to not down when the change is made, what is the current salary, what is the newest salary, those details can be recorded with the help of a trigger. And then a trigger can also be used to perform cascading operations might be whenever you insert a row into transactions table, you want automatically the accounts table to be updated.

I mean, whenever you're depositing amount, you want the balance of that particular account ID to go up in accounts table. All such operations can be performed using triggers. Triggers are divided into two categories. A row level triggers statement level triggers, row level triggers are executed for every row that is affected by DML operation. So if I'm deleting 10 rows, that trigger is FIDE means executed for 10 times on the their hand statement level triggers are executed only for once for each statement. Even though the statement is affecting many roles, the trigger is executed only for once.

And then the triggers might be before triggers after triggers, or they could also be instead of triggers, before trigger is executed before the actual operation takes place. After trigger is executed after the operation is completed, and instead of trigger is executed instead of the actual operation, so we don't perform the operation. Instead we just run instead of trigger whereas before trigger runs before the operation is applied. And if you are not interested in proceeding with the operation, you can fail Before trigger as there is a the operation is aborted after trigger is executed after the operation is first completed after trigger is executed after the operation is completed so that we can take some action after the operation. This is how we create a trigger using create trigger command. While you're creating a trigger, you've got to mention a couple of things.

Not all of them are mandatory name is what do you need to give and always try to give a meaningful name so that by looking at the name, you understand what it is that the trigger is trying to check or perform and what is the table associated with the trigger and whether it needs to be fired before or after, and what DML command should fire it. Is it update delete or insert or is it a combination, you can even have one trigger fired on multiple DML operations and whether it is a row level trigger or statement level. So, are you going to execute the trigger for every row that is affected are are you going to executed only once for the whole statement, you can also specify whether you want the trigger to be fired only in in particular condition. You can say you want trigger to be executed only when a condition is satisfied.

Of course, a condition is related to the row or the table we are dealing with. And then the actual PL SQL block and this is where we give the Pl SQL block that is executed whenever the trigger is fired. Well, triggers are automatically executed, you never call them explicitly. That's a big difference between procedures, functions and triggers. Triggers are automatically executed automatically FIDE by Oracle. It's not something you do explicitly.

Here are the commands you can execute in the context of a trigger. The body of the trigger means the Pl SQL block, the body of the trigger can execute any DML command. And then also the Select command with ain't. Now, this is the flowchart. If you look at the flowchart closely, when you execute a DML command, like anything DML command, update delete insert before the command is executed, Oracle checks whether any before trigger is existing If no, it'll straight away Go and execute the statement. But if the trigger is present, the trigger is executed.

But at the end of the trigger, it checks whether the trigger is successful. What do you mean by that? Well, trigger is successful if the block is successful. If PL SQL block is successful, then the trigger is successful. And remember, every trigger is associated with a PL SQL block. And if the block is successfully terminated, we consider trigger to be successful.

What do you mean by a block is terminated successfully. in one of the previous lessons we discussed, every PL SQL block is terminated either with exit that is success or unsuccess. Success access means that no exceptions are raised are excellent options are raised but they are handled unsuccessful exit means there is unsuccessful termination because of an exception not being handled are you're explicitly raising APPLICATION ERROR. Now, once the trigger is successful we proceed if trigger is not successful, then we are going to roll back all the changes made and stop the process here. And the same is done for after trigger. What we do for before trigger the same we are doing for after trigger.

So, again we check whether it is existing, we executed and if it is successful we proceed otherwise we again roll back and stop. That means both before trigger and after trigger can abort the operation and roll back Whatever changes you have made to that point. Now, let's get to a small example. This is our first example. I want to create a trigger which makes you the hire date of an employ is never greater than system date. You can't have higher date after system date.

So I have a trigger and this is the name of the trigger and this is the DML command. In fact, we want to have two DML commands insert or update. So the trigger will be executed whenever you are inserting a new row and also when you are updating hire date, this trigger is not to be executed if you are updating salary because there is no change to hire date, so no point in checking for invalid value in hire date. So this trigger is executed for insert and update of hire date. And the table is employees. And you must give this to say you're asking for roll level trigger.

And if you don't give that the trigger becomes a statement level trigger, but we must have a role level trigger because from each row I want to take higher date and check whether it is valid. Well, if the hire date is greater than c state, a trigger is only checking for invalid cases. Because if everything is valid, there is nothing for trigger to do. So I want to know whether hire date column is greater than six date and this is exactly how you access hire date. You have to give Collin new dot and new is considered To be correlation name. So color new is how we refer to the new row.

And then higher date is the column in that row. And if it is greater than six date I RAISE APPLICATION ERROR. That is how I explicitly terminate this PL SQL block unsuccessfully. And when it is terminated unsuccessfully, the operation is aborted here, and it won't proceed any further. And it also rolls back all the changes made in this transaction. Now, this is how you create a trigger.

Let's go and test it. Let's create the trigger and here is my trigger. And we just execute this and you should see a message saying trigger created. So it says that triggered so and so is compiled means all is well. No issue. Then if you try to change the hire date of any employ in such a way it's greater than six day, it's going to fail.

Now, I try to execute this and there you are, the trigger is very clearly throwing a message saying hiring date cannot be after system date. And you can see where exactly the problem occurred. This is the trigger where the problem occurred and the error message and the error number can be seen here. So once you create a trigger, anytime you make changes to the table, related to the trigger, the trigger is automatically fired. And Oracle will then decide what to do based on the success of the trigger. If the trigger fails, the operation fails.

If trigger succeeds, we proceed further. So that is about how you create a trigger. Now going By this is another trigger where we want to know whether the start date in job history for unemployed is on our after hire date because job history indicates the jobs there implies did in the past and no employ could have done a job before he joined. So his start date in job history cannot be before the hiring date. So whenever you're trying to update or insert a row into job history, whenever you insert or update the start date, I want to check again it's a row level trigger and the table is job history. And what I need to do is find out what is the hiring date of the employ we are using In our insert command or update command, if I can know the hire date of the person, then I can compare the start date with the hire date.

So if you're updating the start date of employ one or two, then I need to find out what is the hiring date of one or two, and then check this with the hire date of one or two. If the start date is before hire date, then we are going to RAISE APPLICATION ERROR and stop the process. So this is the basic concept of trigger. It is use it to check for conditions and if conditions fail, or succeed depending on what you are putting as a condition. You have to RAISE APPLICATION ERROR and stop the process. So if that's true date is before the hire date, we need to RAISE APPLICATION ERROR and if it is greater than or equal to hire date, then there is nothing to be done.

We just ignore it. Triggers need not do anything if everything is fine, they should raise errors and stop the process only if it finds something incorrect. Now, how do you test this, let's get back to this here is my second trigger. And this is the trigger and we need to first executed go ahead and run and clear all that run. It's done. So the same trigger and we executed in case if it is already there, it will be replaced because we are giving our replace.

And now I'm trying to change the start data of some employ. Of course the employee should have a role in job history. I have a role for what not to in job history. So I do this. And I'm setting the start date too far before the actual hiring date, because we have hired date somewhere in 2000. But I set the start date to 1990.

Let's see what happens go ahead and run and it's going to throw an error and the error is again very clear and the trigger name is clearly given. So, this is another business rule, which cannot be implemented with a normal integrity constraint, because check constraint cannot go to a different table and take the data from there. And this is what we have to do with a trigger. So those are the use cases for trigger and coming back. If you see the next one is about the correlation names. The correlation name, called the new and old are used to access the data that is currently present and the data you're putting into that row.

So new always refers to new data. Old refers to existing data. But depending on the DML operation, new is not available. Sometimes old is not available sometimes. As you can see, it's very simple. New is not available if you're dealing with the Delete, because while you're deleting there is no new data.

Similarly, when you're inserting a new row, there is no old data. That means only for update we have both new and old because we have the old data and we have the new data which is replacing all the data and another employee endpoint only if you're using a row level triggers, these correlation names are available to you, because you are executing trigger for each row in case of statement level trigger, where we are not taking each row into account, these correlation names are not available. Now, this is a small example to demonstrate, how we can use correlation names and this is a trigger that prevents any change to salary in such a way the salary is decreased. So, we say the salary cannot be decreased, it can go up or it can remain the same but it cannot be decreased. So, if you are trying to set the salary in such a way that the new salary is less than the old salary, we raise error and stop the process and this is Before update trigger, and it is executed only when you make a change to salary.

So, we are going to check whether the salary is less than the old salary. If so, we stop otherwise we proceed. This is how we create triggers. And in all these cases, be aware, there is no alternative. You can't achieve this with any integrity constraint. Well, you can include these conditions in procedures etc.

But what if somebody is directly modifying your table, then trigger is the way to prevent it. So triggers play utmost important role in the context of implementing business rules. And then comes the statement level trigger. This is not a trigger related to each row. This is the trigger related to the statement. So when To execute a statement, whether it is insert, delete or update, then the trigger is fired.

Now I'm saying don't execute it for each row so you don't have that clause for each row. That means it becomes a statement level trigger. And I want to know what is the day today. If the day of your system date is one that means Sunday because in Oracle the week begins with the Sunday. If that is the case, then I want to stop the process with this message. So I say no changes can be made on Sunday.

But if it is not on Sunday, then we can proceed. And if you observe closely, we are not bothered about what change you're making. We are only bothered about whether it is Sunday or not. Because if it is Sunday, whether the change is valid or not. Still, it is not allowed. So let's get to SQL Developer.

And here is my simple example. Now I'm trying to do it in this way. I made a small change to it. I'm saying not change can be made to employ stable, no change means neither insert, nor update nor delete. Okay, no change can be made before a particular hour. Let's say in my system, now it is 10am.

So I just tried to change it to something like lemon. So no change can be made before 11 o'clock. So if I try to do it in that way, let's go and create it. And you're not allowed to now make any change to employ stable because now the system clock is saying it is around 10 o'clock. So if you try to apply You're going to fail, let's try updating something, update employees table set salary to salary into 1.1. It doesn't matter what change you're making because we are not allowing a change at all.

So where I say employee ID is equal to triple one. And after this trigger, if I go ahead and try this, and it is telling me no changes can be made before 9am that's okay because the message is like that, but we are actually checking whether it is before level. So only if it is after level or at level 10. We are allowing any change to be made. So you can prevent any change to a table based on some condition, which is not exactly related to the data in the row, like the day of the week or then might be based on Who is making the change? Okay, and what time they're making changes, and so on.

These are all to complement what is already there in database. I mean, we do implement security with privileges. We don't allow anyone to make changes if the person is not authorized. But there might be some cases where you can make change only before level but not after 11 that cannot be incorporated using simple security privileges. That's where your triggers chip in. So statement level triggers are checking for conditions not related to data related to something external to table and data.

So in this lesson, we understood how to create a database trigger a very important concept every database programmer must be well versed with triggers concept because Without triggers, your database is never complete, because a lot of business rules might not be implemented at all. And that allows invalid data to creep into your system. And we understood the trigger so flowchart so how the flow goes, we understood the new and old correlation names. We saw the difference between row level triggers and statement level triggers. And this is the first part of triggers. And we just got started, we understood some use cases for triggers the syntax and how the triggers work, and more about triggers will be discussed in the next part.

See you there.