Hello friends! Today I have brought you another interesting topic. This can be someone’s most hating chapter. After reading this article I am sure it will changed. Today our topic is how to write database triggers. Actually database triggers are very easy chapter. You might not agree with me now since you don’t understand. When you understand it you will also say it. Clear your mind and read the post carefully and learn. You can score on this topic. So let’s start.
Database triggers play important role in databases. Firstly we will learn about triggers. Then we will learn procedures. I hope this way is most effective and easy. Database riggers mostly connected with query execution. In this chapter we mostly talk about querying. If you are not good in SQL query writing this might be a one point you don’t get this chapter. I will try to include simple SQL queries before write queries which we use most frequently.
By the end of this post you will able to understand what is a database trigger. What are the types of triggers. Why we use SQL triggers. You can learn triggers explain with example from our another post. Here’s the link. https://myexamnote.com/triggers-questions-and-answers/ . Then we’ll start.
What are database triggers
Database trigger is an event. It will run automatically. Manually we don’t have to execute a trigger. We can write triggers in database. We can either write complete sql code or some database management system has user interface wizard option. These triggers handle query execution level job. Before learning how to write database triggers it is important to learn what it is.
There can be things in a system that we wanted to do. As an example when we add a new batch to the inventory stock has to updated. The new batch has to add a new record to the batch table in the table. The product quantity in the product table also need to updated. Simply we need to update two database tables when a action happens. If think how we do it in code level we have to write a nested sql. After insert query for the batch table execute product table update query has to execute. In simply code level it is like this.
$sql batch = INSERT INTO batch(bat_id,prod_id,bat_sprice,bat_cprice)VALUES(?,?,?,?);
$stmt_batch = $dbobj->prepare($sql_batch);
$stmt_batch->bind_param("sisd",$bat_id,$pro_id,$sel_price,$cost_price);
if(!$stmt_batch->execute()){
echo("0,SQL Error :".$stmt_batch->error);
}
else{
$sql_pro_upd = "UPDATE tbl_product SET prod_qty=prod_qty+? WHERE prod_id=?;";
$stmt_pro_upd = $dbobj->prepare($sql_pro_upd);
$stmt_pro_upd->bind_param("is",$pro_qty,$pro_id);
if(!$stmt_pro_upd->execute()){
echo("0,SQL Error :".$stmt_batch->error);
}
$stmt_pro_upd->close();
}
$stmt_batch->close();
Rather typing this type of long nested sql we can do this in the db level. Then this one part of nested query will execute in database processing level. This is a usage of database triggers. We don’t have to execute database queries in the code level. Database can handle it by itself. Triggers execute when meets a condition or event. We can include what is the condition or event when we building the triggers. According to the above example that is after batch table updated. There are properties we include in trigger. They will tell to the trigger when to execute exactly.
Database trigger types
We have different trigger types. They are as below mention.
- DML Triggers – These are the triggers which includes DML operations like UPDATE, INSERT operations.
- DDL Triggers – These are the triggers which includes DDL operations like CREATE, ALTER,DROP operations.
- System event Triggers
- Instead of Triggers
- Compound Triggers
Usage of Triggers
We can use triggers to different tasks. They are,
- Auditing
- Enforce referential integrity
- Preventing invalid transaction
How to Write Database Trigger Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE| AFTER | INSTEAD OF}
{INSERT OR | UPDATE OR | DELETE}
[OF column_name]
ON table_name
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
WHEN(condition)
DECLARE(declaration)
BEGIN
{Execute sql statement}
EXCEPTION
{handle sql exceptions}
END;
Explanation of the syntax
After seeing the syntax now we will learn what is meant by that syntax.
CREATE – The syntax start with this word. This is the sql command to create a trigger.
REPLACE – Rather saying create if we want to change an existing trigger we have to use this replace command. CREATE is to make a new trigger.
BEFORE/AFTER/INSTEAD OF – Earlier I told you we can define states when to execute the trigger. That is denote by these worlds. If you remember earlier example there product table needs to update after inserting to the batch table. Here highlighted the world after. We are writing trigger to update the product table. That should happen once the batch table updated. So we can use word AFTER for this trigger. Like wise we can use one of these three words in the trigger to mention the execute details.
INSERT/UPDATE/DELETE – Previously we told what is the instant for the trigger. We didn’t tell what is the operation. Because so many SQL operations runs in tables. We need to exactly tell after or before which SQL operation trigger has to fire. So that is denoted by this line. We can say AFTER INSERT, AFTER UPDATE, BEFORE UPDATE what is the event to fire the trigger.
OF cloumn_name – Sometimes triggering event might not be the change to the whole table. SQL operation might affect to only one column in a table. So in that case we can use this operation to include that event to the trigger.
ON table name – Here we can put what is the table that will effect to fire the trigger. In earlier example the changes to batch table triggers the changes to the product table. So triggering table is the batch table. So here we put the table name which is caused to fire the trigger.
RFERENCING OLD AS o NEW AS n – When a trigger fire on a table there is two states. Please clear this the table which trigger will fire is the same table that cause to fire. According tot the pervious example we took batch table is the table that trigger will fire. After firing the trigger it will do some changes to another table. Yet that table is not the table trigger will fire. So make it clear on trigger fire table.
So when a change happen to table to do a task by using a trigger we need some data values. Because of that after firing that trigger we keep old tuple data and new tuple data. Old tuple data we can call from OLD keyword. New tuple data we can call from NEW keyword. A tuple means a row in a table. When we use keyword OLD the query might take lengthier therefore we can put alias. That is done by reference. Then after that instead of using OLD keyword we can call that by using o letter simply. Same applied for the NEW keyword referencing also.
FOR EACH ROW – From this command you can decide whether the triggers has to fire on each row or not. If you say each row trigger will fire every row. So many times. But you didn’t include it then trigger will only fires one time. That time is the the time it met the condition or event.
WHEN – There can be scenarios like this. Even though trigger will fire on the basis of sql operation change to a table we don’t want every time to fire the trigger. Like every time after update or after insert operation happened to the table we don’t need trigger to be execute. We want it to be execute under some condition. When it met that condition only. As an example every time stock decrease we don’t have to add a record to the product reorder table. Even though when stock level is equal to the product reorder level we want a record in reorder table. So we can include this condition to the trigger.
DECLARE – When writing triggers we want to declare some variables. We need to keep some values. Something which we can’t call by OLD Or NEW keywords. For those things we can use this Declare option and declare variables. When we declare the variables we have to put the datatype. They are INTEGER, NUMBER,CHAR,VARCHAR. The datatypes we usually use in SQL.
BEGIN – Here is the place which we include the SQL commands what we wanted to get done from firing the trigger. After the begin keyword we can write SQL command. Most probably it also can be inserting record or updating or deleting operation.
EXECPTION – If you want to handle exception occurs in the SQL you can handle them in there.
END – This is the command to end the trigger. When you write this command trigger is end from here.
Conclusion
So here we come to the end of the post. Today we learned about triggers. I hope this post help you to gain and improve your knowledge. Here we discuss the theory parts of triggers. You might heard a idiom call practice make perfect. If you want to be best in triggers you have to practice the theory and apply it. Doing more questions. Therefore I hope to explain few trigger questions and answers for them from another post. For that please check triggers questions and answers. Please read that also.
As usual if you have doubts or questions need to clarify please comment us and let us know. We can write a post about it. If you like this post please comment and let us know how was it. Share it among your friend then they also can learn these things. Then meet you all in another interesting topic. Till then Goodbye!
Some truly prime articles on this internet site, saved to my bookmarks.
Thank you so much.