Oracle interview Question : what is trigger in oracle

hello friends welcome to the video series on Oracle interview questions for SQL PL sequel developers so in this video we are going to see about three different questions but all three are variables are related to trigger so I thought of putting all these things in a single video so typically like the question something like what this trigger all these things are like I've been a beginner or refresher level however there are questions like the types of trigger or the purpose of children sometimes these are like a little intermediate level or expert level of expertise okay

first we will start with what should be the formal answer for your whitest trigger we through a simple honor analogy then I will talk about what are the types of trigger and the purpose of trigger will try to understand the concept of trigger through a simple analogy then we will see what should be your formal answer for this question okay suppose if a person going for an ATM and he is trying to withdraw in a mode on a deposit in Amman the moment he withdraws are deposited aboard he will get an SMS saying that this

much amount is either credited or debited in his mobile as well as he will get an email saying they're from your account this much amount has been deposited or it has been debited or credited whatever depends on the transaction you will get a message so what happens here is that there are two type of action happens when it is like the person going and doing a task something like he is going and a the depositing the amount or his withdrawing this Ahmad the next is and actually is automatically performed as you can see here so

the the person going and withdrawing or de passing that is their event happen this even automatically triggered an action the moment he withdraws odds are deposits he immediately gets an SMS so this is something like an even based programming the moment an event happens and action associated with that event is automatically triggered so this is a simple analogy in terms of a database side we can just relate it to any database event for example if there is a table let us say there is an account related table which has the information about all the account

and its balance the moment either an update or delete or insert happened to this balance so this is now very much related to the event the moment any event happens on a table we can write a plc equal code which can be automatically fired okay so just see the word clearly this political block can be automatically fired we are not going to fire the moment and insert or update or any event happens in the table this particular piece of code can be automatically fired which can in fact send a mail or it can trigger any

other operation so this is called the trigger okay now let us see a bit formal way of answering for this question a trigger is nothing but a PLC code block structure which is automatically fired whenever event happens in the database okay so the event can either be a DML event something like it insert update delete or it can be a DDL event like create alter drop truncate or it can be system event like log on log of our system start up our system shutdown okay so the moment i event happened the associated trigger because we

can create a trigger for a DML event or a DD element for a system even so the moment an event happens the associated code will automatically get fired so this is called trigger in database technologies okay now let us see like what are other types of triggers okay say mainly the triggers are classified based on the type of event happens those let us first see the first type of trigger called DML trigger any trigger that get fired whenever a data manipulation language just command like insert update or delete happens in a database this particular trigger

will get automatically fire that is the data manipulation language trigger so typically this insert update delete happens on a table so this trigger will sit on top of a table and one more very important point a trigger we cannot manually invoke like a procedure or a function we cannot manually go on executed trigger trigger can will get automatically invoke in terms in case is the even fires okay so the next type of trigger is quite DDL trigger this triggers will get automatically for you whenever a data definition language comment or even occurs something like you're

creating a table or altering a tribal or truncating a table whenever a grant or revoke command that issued or whenever an audit command or revoke commands are issued then this particular trigger will get automatically for it that is the DDL trigger the third type of trigger is called system trigger so any system events like a logon suppose the user is logging on that is a system event same way a user is logging off from the system so that is another system we will we are we are booting up the database are we are bringing down

the service of the database so all these things are system related event so we can write it trigger to get automatically fired whenever any of these system related events happen so those are point system trigger so basically these three types of triggers are classified based on the type of the event as I meant as I told the first event is a DM element so we have a DML to the ii is a DDL event which fires a DDL trigger and third is a system event which invokes the system trigger we have few other types of

trigger the one mode is called instead of trigger so typically this instead of triggers are written on top of a view because the use will not have any data as its own it in fact such as the information from this underlying base table so we can write a trigger on top of the view which can internally redirect the update or insert or delete statements to the underlying table so those triggers are called instead of triggers because sometimes this question also will be also in interviews like what is the difference between a DML trigger and I

instead of the trigger because DML triggers are written on top of a table whereas instead of triggers or attack on top of views so there is another type of en trigger called compound trigger okay so this compound trigger is nothing but it is the same DML trigger but all the timing events all the informations about a DML triggers can be written on in one single compound trigger okay so the comport to the functionality is exactly similar to DML trigger but the only difference is in a DML trigger will be writing by individual to this for

each timing operation are each DML event however in terms of compound trigger with one single trigger we can write all the information to whatever we are going to cover in the DML together in fact there are a few subtypes of this too this probably I will talk about the subtypes of DML to those or any other to us in a bit separate video okay I think from an interview aspect if you can cover these five types of triggers and if you can say when these particular triggers will get fed I think that should be enough

now we will see what are the purpose of the trigger typically the triggers are mainly there are various purpose of the triggers are being used however I'll try to cover like most of the things I think it's enough if you say like four or five types of four or five purposes but however it from a working aspect you should know even bit more and you should know all the aspects of sugar the first one is like auditing so whenever any changes happens in a database data typically we will audit so there is one of the

technology by which one of the means by which we can harden the data the next is like enforcing complex referential integrity whatever the integrity constraints which we cannot enforce through a foreign key primary key relation that we can enforce through the triggers the next is slightly logging as I mention it's very similar to like auditing like whenever you want to capture who changes the particular data or the history of changes you can use the triggers for logging the informations then they like enforces security of transactions that was in particular table you don't want anyone to

delete a record then you can even block the delete transaction itself saying that whenever someone deletes it just throw an exception so that you are completely blocking the daily transaction so using triggers you can enforce the security the next is like a data replication to multiple table like whenever a record gets inserted into one table the same time you want to copy the same data into multiple tables so you can write trigger which is get automatically fed whenever you insert happens and in and it just copies the data to the multiple table as you need

next is like like preventing invalid data or transaction as I told already you can write a trigger to either block the insert or day update or delete or whatever the transaction you can even give you a logical check saying that if someone try to update a value more than like one lakh debit or credit amount then you can say that it is like invalid data so you see a conditional checks also you can block so it's very very useful in fact like many places will be using triggers to validate the data also the next is

like auditing like DDL transactions or DDL statement like typically like who created a table or when a table is created when a table is drop or who is modifying the table are typically sometimes you can use the DDL triggers to block dropping off the table because this is one typical use cases I don't want anyone to drop any object from my database then you can just go and create a DDL trigger which block the drop statement transactions okay the next select system triggers mainly used for auditing and lying sake that who is logging at what

time how much time a particular session is active all these things you can capture using the system triggers same way like log on log of triggers as well as like startup shuttle triggers are mainly used in case of whenever you want to clean up something whenever a database comes up you want to clean up set a number of temporary tables then you can use the startup / trigger same way like I don't trigger so whenever you are bringing down the database or whenever a users log off from the DB then you K you want to

delete some transactions from the table then you can write these informations in the system triggers and you can use this triggers to automatically invoke any other procedures or functions or any other jobs okay in case if you have any questions to get answered you can just share it in the comment section or you can drop to this main lady if you learned something new please like this video and subscribe stay tuned for more new feature videos interview questions cons of videos tips and tricks thank you