Hi! my dear friends. We discussed about the triggers theories in another post. Yet we couldn’t discuss questions from triggers. I promised to write a post about trigger questions and answers. So here I brings you a post about triggers questions and answers for them. let’s solve some questions. Previously also I told you this is a chapter that you have show your query writing skills. If you lack in SQL knowledge my dear friend you have to upgrade it. Before starting questions I will mention few frequently used INSERT, UPDATE, DELETE queries. Because in most triggers and stored procedures we write those SQL queries.
Mostly used SQL queries
SELECT QUERY
We use select query to select attributes from a table. In simply it is a vertical cut from a table. So if you need an attribute to be use you can use SELECT keyword. If you want to filter your result you can use WHERE clause. From WHERE clause you can give from which type of data you want. As an example if you want to see unpaid order you can say WHERE order status = 0. If you want to see marks of student Nimal that you can write WHERE stname = Nimal.
basic syntax
SELECT * FROM Table nameSELECT column names FROM Table name
SELECT column name AS name FROM Table name
These two are variations for basic SELECT query. First query is the basic query. From it we can retrieve all the columns in the table. Using the second query we can specify the columns we need. From the third one we can assign column data to another variable.
UPDATE QUERY
UPDATE table name SET column = value1, coulmn2 = value2 WHERE conditionDELETE QUERY
DELETE FROM table name WHERE condition.Triggers questions and answers
So since we know basic sql quires now. Let’s start the trigger questions and answers. Today you can learn what is database triggers with examples. How to write a SQL trigger.
Question 01
The tables given below are for maintaining an inventory system. There are two tables for maintain the inventory. There details are as follows. When a product’s current quantity = reorder quantity it should mark as a reorder product. Which means a recorder need to add to the reorder table. Write a trigger for the above scenario.
tbl_product(pro_id,pro_price,pro_sprice,pro_qunatity,reorder_qty)
tbl_proreorder(id,pro_id,quantity)
Answer 01
CRAETE TRIGGER reorder_placement AFTER UPDATE pro_quantity ON tbl_product FOR EACH ROW
WHEN(pro_quanity = reorder_qty)
BEGIN
INSERT INTO tbl_product VALUES(NEW.pro_id,NEW.reorder_qty);
END;
In this example let’s assume id in reorder table is auto increment. So we don’t have to include it when inserting. Here we have to use when condition. Let’s take the trigger name as reorder_placement. Sometimes a questions do not provide all the details we need to write the trigger or procedure. Therefore we have to assume and define our own data types or variables or the things we need. Hence do not be panic. Assume the details and write the sql code.
Question 02
One use of triggers is maintaining the referential integrity constraints. Consider there is an Employee table. Employees works in a department. There is a department also. When a department was deleted the employees on that department also have to delete. Write a trigger to maintain this referential integrity for on delete cascade. The details of the tables are given below.
Employee(name,Age,Address,Depid)
Department(Depid,Depname)
Answer 02
Earlier we discussed referential integrity is one usage of triggers. From this example you can learn this. Here when a department delete automatically employees has to be deleted. Because if not there can be employees who belongs to the department which is not currently exists. Therefore to maintain accuracy and integrity this automatic deletion has to take place. When Parent remove child also need to remove. You can see parent table is the department table. Employee table is the child table. Department id is the foreign key of the employee table. So it is referencing which employee works to which department. Let’s look at the answer. This answer is simple. Just go with the syntax. No variables, conditions needed. Since there is no name for trigger we will take a name we like.
CREATE TRIGGER delete_emp AFTER DELETE ON Department
FOR EACH ROW
BEGIN
DELETE FROM employee where Employee.Depid = OLD.Depid;
END;
Question 03
There is a salary increment in an organization for thier employees. Salary was increased from 10% of thier salaries. The employee table details are given as follows. Write a trigger to show salary difference of employees after the salary increment.
Employee(name,payment,empid)
Answer 03
This is little difference question from other questions. So far the questions we solve inside the begin statement we wrote sql operation. Yet here we don’t want to write those kind of thing. Because trigger is to show the salary difference. Not to insert or update, delete a table. Here we need to declare variables. For what think. To take the difference. We can take old salary from OLD keyword. Same as we can take new salary from NEW keyword. We don’t need variables to store those values since same record is updating.
We don’t have to bother about 10% here. If you are going to calculate it then you are misleading in the question. We write trigger after updating the table because to fire the trigger there needs to be a triggering event. Even though it is mentioning 10% you don’t have to think about it. Here you can learn new thing as well as. That is we use DBMS_PULINE() to show an output in sql. Remember in here you have to deduct new value from old value. Because after a salary increment new payment is the highest value. Old value is the lower value. If you did the vise versa it is incorrect. Careful about small things also. Let’s look at the answer.
CREATE TRIGGER show_sal_diff AFTER UPDATE ON Employee
FOR EACH ROW
DECLAIRE sal_diff NUMBER
BEGIN
sal_diff = NEW.payment - OLD.payment;
DBMS_PUTLINE(NEW.payment);
DBMS_PUTLINE(OLD.payment);
DBMS_PUTLINE(sal_diff);
END;
Conclusion
Today we have discussed few triggers questions and answers. If you want some more about solve from triggers questions and answers please comment and let us know. As well if you need lengthier explanation than this no problem we will do it for you. If you learn something from this post please comment and let us know. Your comments are our motivation and pleasure to see those. Thank you for all being with us and reading our posts. If you seems this is a good post share it among your friends then they also can learn. Then we will meet with another interesting post in future. If you are interested have a look on our database stored procedure post. Till then goodbye!. If there is a topic you want to know and still we didn’t posted please comment and let us know. We will release a good post about it. Thank you!
Nicely explained trigger theories. Thank you sooo much..
so much excellent info on here, : D.
Thank you so much.