Hi. In this lesson, we go into more details related to triggers. In the previous lesson we understood what are the applications of trigger, how to create a before trigger after trigger and the difference between statement level and row level trigger. Now, let us start with a different kind of trigger called instead of trigger a before trigger is executed before the DML command is executed. After trigger is executed after the DML command is completed, but instead of trigger is used to execute some commands instead of the actual command. That means, it does not execute the given DML command.

Instead of the actual DML command it executes the trigger. So this can be created only on views not on tables. And it is used on views where we cannot directly manipulate view. If you can recollect from our discussion earlier about views, not all views are updatable. There are views that cannot be manipulated. That means they cannot manipulate base tables because they don't satisfy the rules related to making a view updatable.

We see an example here. And this is a view called employed job, which is taking data from two tables. Generally, when a view is based on multiple tables, it becomes read only. And this view is taking data from employees and jobs. And also in the context of joint views, the views that are based on join we can And played only the key preserved table. Key preserved table is the table whose primary key is still unique in the view.

So if you take this particular example, employees and jobs are the base tables and employees tables primary key that is employee ID is still unique here. But job ID, which is the primary key of A job stable is no longer unique, because there might be multiple employees belonging to the same job. So job ID is repeated in this view. So we can say employees is the key preserved table for employed job and jobs is not a key preserved table. So in this context, if I tried to update any column of employees table like first For example, that's fine, because you can update a key preserved table. But if I try to update a job title, which is coming from non key preserve table, because jobs is not a key preserve table, or racquel throws an error.

And you can see that clearly here. I'm trying to update a job title, which is coming from jobs. And I'm using my view employee job and that throws an error and the message very clearly says, you cannot modify a column that is related to non key preserved table in this case, jobs. So what is the remedy for this? I want to update a job title using employ job view. So if you want to update a column in a non key reserved table, Then you need to go for instead of trigger.

So this is the scenario. Our view is based on two tables, but implies is the key preserved table. So I can make changes to that through the view. But I can't make changes to jobs because that is not key preserved table. The solution to this is creating an instead of trigger. So create an instead of trigger on employ job because instead of triggers can be created on views.

And then I'm trying to bypass the actual command. So I'm not going to execute the personnel update command. For example, this is the original update command, and we don't want this to be executed. Whenever you give this command, we want Oracle to execute this block and that is our installation. trigger. When this block is executed there, I want to update employees table, change the first name to new first name.

And I want to change jobs table and set the job title to new job title. So, normally you cannot update jobs table using this view. But with the help of instead of trigger on employ job, I'm able to update both employees first name and the job title. job title is not updatable otherwise, but with the help of instead of trigger we are able to do that. So this is the application of instead of trigger. So if you create the trigger and give this command, it works, but if you try to do the same without creating any incident of trigger, then you get the error.

Saying cannot modify the column which is related to non key preserved table. So instead of triggers are quite different from normal triggers, we have seen before and after triggers before, but those two were executed before and after the actual DML command respectively. But instead of trigger is executed, bypassing the actual DML command. So pretty powerful, but it's only in the context of views. Then, at times when we have a single trigger fired for multiple DML commands, let us say I have a trigger which is executed both in case of insert and update. And then I want to know whether the trigger is fired because of inserting our because of updating Well, that's the reason why Oracle is providing three key words which you can use in the context of your trigger.

So inside the trigger, if you say inserting, it returns true if the trigger is fired because of insert command. And it returns true if you're executing update command and your trigger is fired because of update command for updating keyword. And deleting keyword will return true if the trigger is fired because of delete. Let's talk about an example related to inserting, updating and deleting. I want to create a trigger that is going to log all the changes you make to employees table. So whenever you're trying to insert a row I want to write into implies log with employee ID and remember for insertion We have only new ID no old ID.

So we use new and we put the system date and time and I, I assume there is a table called employee log with the three columns, employee ID, date and time. And then the kind of operation so that's going to be I in case of insertion. If it is not insertion, if the trigger is fired because of we updating implies table then this is true and we are going to put you as the operation and we again use the employee ID and sis date. And here you can use either new or old because update is providing both correlation names. But when it comes to the last one, we are trying to put D and we must use old because we don't have any new employee ID when we are Dealing with the deletion. So you have to be careful about the correlation names, because the operations dictate what correlation names are available to us.

So this is an example for inserting, updating and deleting keywords which are using to know whether the operation is insert or update or delete. Now, there might be cases where you want to delete a trigger all together because it's no longer relevant. But they might be also cases where your intention is not to drop the trigger all together, but only to disable it for the time being. Why Why do I disable a trigger? Well, you disable the trigger because you strongly feel in the current context that trigger need not be executed. If you feel the trigger need not be executed because the change Just you're about to make are all valid changes, no need for trigger to verify the changes, then you can disable trigger to improve performance.

It's all about performance. Because if I'm trying to change something like thousands of rows, I don't want that trigger to be fired. And a lot of time is spent on executing trigger for each row. So I would like to disable the trigger. Before I execute a command like this, which is changing the first name, and I'm just changing the first name to init cap. That means every first letter of the word is in uppercase, remaining in lowercase.

This is not an operation that could inject any invalid data. So before I do this, I want to disable the triggers related to imply stables updation Well, in such a case You just need to go and use disable option, either with altered trigger. In that case, you give explicitly a particular trigger name. Or you can disable all the triggers related to a table. When you say ALTER TABLE, employees disappear, disable all triggers, all triggers are disabled, anytime later, you can re enable all the triggers. And if you want a single trigger to be disabled, just give the trigger name, disable and perform whatever operations you want to perform.

And again, you can re enable the trigger. Of course you got to be the owner of the trigger to do that, not somebody else. So this is how you can disable and enable triggers depending on your requirement. Of course if you want to drop the trigger because you feel the business rule that you make In the trigger is no longer applicable. For example, we didn't want anyone to make changes on Sunday. But for some reasons that is no longer relevant, we can drop the trigger altogether.

Then, in the recent past, we got a very powerful kind of trigger called compound trigger. This is one trigger that can perform four different actions at four different events. Like before row before statement after row after statement. Actually, this would need four different triggers. In olden days, if you had to create a trigger for before role level, and then before statement, you would have to have two different triggers. The problem with the two different triggers, they are two different PL SQL blocks.

So if I have to access some data that Common to these two, it was difficult. So compound trigger is saying I put all of them in one PL SQL block. So the before roll level before statement, and after roll level, and after statement, we are going to put all these four in one block. And then there you can have some data also if you like. And the advantage is all these blocks get access to this data. So it's one PL SQL block with four different sub blocks, and all of them are having access to some variables.

So this is ideal for implementing some complex business logic. This is called compound trigger. It's one trigger with four different sub blocks and one for before row one for after row. And one for before statement, and the last one for after statement. So where do we use this? Do we need this in any case, let us see a use case here.

Well, this is the syntax for that, you just need to give compound trigger. And that makes a tech compound trigger. And here are the declarations. And these are the blocks first to block and second block. And third, and fourth, these blocks can have the code and all these blocks can also access the data that you declare here. All the variables that are there that are declared here can be accessed from anywhere.

Now here is my use case. I don't want to increase the salary of employees by more than 10,000 in a single update command. So in a single change, I don't want to actually cede the total hike of 10,000 How do I do that? Because this is difficult if you have to go without compound trigger, but compound trigger made it simple. So here we are, we create a variable called Total and this total variable is set to zero in before statement trigger. So before the statement, the UPDATE statement is executed total is zero.

And then before each row I have nothing to do, if there is nothing to be done in a particular sub block, you can just ignore it but you have to give now without that it is invalid. You can't leave any block totally empty. So you can just give now statement, then bought in after row. You see after each row, we want to take the difference between the new salary an old salary, and that is added to total and put into the total variable. So this is where we are adding the hike to total. And now comes the last one the after statement.

This is where I want to know whether the total is exceeding 10,000. If so, I throw an error and stop the process, some saying total hike in salary cannot cross 10,000. So this is the use case where you can apply compound trigger. Because if you want to do the same thing without a compound trigger, it would be I don't say impossible, but would be much more difficult than this. Now, let us see how we can apply this and here is my compound trigger. I'm trying to make some change and here if I try to make a change like this, so is this single Update command in which we are increasing the total salary of all the employees by more than 10,000.

Of course, because we are going with the first 10 employees, and we are increasing the salary of each employee by 2000. So obviously, the total hike is going to exceed 10,000. But without a trigger, this will go ahead and it is updating 10 rows. But now I need to go and roll back because I don't mean to make such a change done. And now let's create the trigger if I create the trigger successfully, and if I now go and execute this, it's trying to increase the salary of each employee by 2000. And it is updating 10 employees salary that's going to be 20,000.

But that's bigger than what we can allow with Because we can allow only 10,000 So, this is going to be aborted because the trigger fails go ahead try There we are. So, total hiking salary cannot cross 10,000 that is the message and this change is aborted. That means, the compound trigger is what is failing, and as a result the whole operation is aborted. So, compound triggers can play a very important role, relatively advanced, but you may need it in some specific cases. So, that's about the compound trigger instead of trigger. So, this is where we talked about how to raise the salary by 500.

That's okay because 500 is not going to cross the 10,000 threshold we said, but if you try to increase salary by 2000 for 10 months It's going to cross that. So, this is how you can test it this is allowed, this is valid, but this is not allowed because this is crossing the limit. So in this lesson we started with instead of trigger instead of trigger is used only with the views, specifically the views which are not allowing you to update base tables. You can overcome that limitation by using insert of triggers. Then you can know whether the trigger is fired because of inserting or updating or deleting using those keywords. Anytime you can enable or disable a trigger, you have to decide whether to disable or not to improve performance in some cases.

If you want, you can, anytime you can re enable it. Drop a trigger if it is not making sense anymore. compound trigger is one trigger with the four events before row before statement after row after statement. And we have seen how we can use it in order to prevent a change to salaries in such a way the total hike is crossing 10,000. So this is about triggers. And we have seen it in two parts, because triggers are pretty important to maintain integrity of data.

That's all for now. See you in the next lesson.