Are stored programs, which are automatically executed or fired when some events occur.
Triggers MCQs : This section focuses on "Triggers" in Pl/Sql. These Multiple Choice Questions (MCQs) should be practiced to improve the Pl/Sql skills required for various interviews (campus interview, walk-in interview, company interview), placements, entrance exams and other competitive examinations. Show
1. __________ are stored programs, which are automatically executed or fired when some events occur. A. Procedure View Answer 2. Triggers can be defined on the? A. table View Answer 3. Which of the following specifies when the trigger will be executed? A. BEFORE View Answer 4. Which specifies the column name that will be updated? A. For col_name View Answer 5. OLD and NEW references are not available for table-level triggers. A. TRUE View Answer 6. Which of the following are benefits of Triggers? A. Generating some derived column values automatically View Answer 7. In which event(s) trigger is executed? A. DDL View Answer 8. Which of the following is not an advantage of trigger? A. Various column values are automatically generated by triggers View Answer 9. Which
clause is used to create trigger on a view? A. BEFORE View Answer 10. [ON table_name] specifies the name of the table associated with the trigger. A. Yes View Answer Discussion What is Trigger in PL/SQL?TRIGGERS are stored programs that are fired by Oracle engine automatically when DML Statements like insert, update, delete are executed on the table or some events occur. The code to be excecuted in case of a trigger can be defined as per the requirement. You can choose the event upon which the trigger needs to be fired and the timing of the execution. The purpose of trigger is to maintain the integrity of information on the database. In this tutorial, you will learn-
Benefits of TriggersFollowing are the benefits of triggers.
Types of Triggers in OracleTriggers can be classified based on the following parameters.
So each trigger is the combination of above parameters. How to Create TriggerBelow is the syntax for creating a trigger. CREATE [ OR REPLACE ] TRIGGER Syntax Explanation:
:NEW and :OLD ClauseIn a row level trigger, the trigger fires for each related row. And sometimes it is required to know the value before and after the DML statement. Oracle has provided two clauses in the RECORD-level trigger to hold these values. We can use these clauses to refer to the old and new values inside the trigger body.
This clause should be used based on the DML event. Below table will specify which clause is valid for which DML statement (INSERT/UPDATE/DELETE).
“INSTEAD OF trigger” is the special type of trigger. It is used only in DML triggers. It is used when any DML event is going to occur on the complex view. Consider an example in which a view is made from 3 base tables. When any DML event is issued over this view, that will become invalid because the data is taken from 3 different tables. So in this INSTEAD OF trigger is used. The INSTEAD OF trigger is used to modify the base tables directly instead of modifying the view for the given event. Example 1: In this example, we are going to create a complex view from two base table.
Then we are going to see how the INSTEAD OF trigger is used to issue UPDATE the location detail statement on this complex view. We are also going to see how the :NEW and :OLD is useful in triggers.
Step 1) Creating table ’emp’ and ‘dept’ with appropriate columns CREATE TABLE emp( emp_no NUMBER, emp_name VARCHAR2(50), salary NUMBER, manager VARCHAR2(50), dept_no NUMBER); / CREATE TABLE dept( Dept_no NUMBER, Dept_name VARCHAR2(50), LOCATION VARCHAR2(50)); / Code Explanation
Output Table Created Step 2) Now since we have created the table, we will populate this table with sample values and Creation of Views for the above tables. BEGIN INSERT INTO DEPT VALUES(10,‘HR’,‘USA’); INSERT INTO DEPT VALUES(20,'SALES','UK’); INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN'); COMMIT; END; / BEGIN INSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30); INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ; INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10); COMMIT; END; / Code Explanation
Output PL/SQL procedure completed Step 3) Creating a view for the above created table. CREATE VIEW guru99_emp_view( Employee_name:dept_name,location) AS SELECT emp.emp_name,dept.dept_name,dept.location FROM emp,dept WHERE emp.dept_no=dept.dept_no; / SELECT * FROM guru99_emp_view; Code Explanation
Output View created
Step 4) Update of view before instead-of trigger. BEGIN UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’; COMMIT; END; / Code Explanation
Output ORA-01779: cannot modify a column which maps to a non key-preserved table ORA-06512: at line 2 Step 5)To avoid the error encounter during updating view in the previous step, in this step we are going to use “instead of trigger.” CREATE TRIGGER guru99_view_modify_trg INSTEAD OF UPDATE ON guru99_emp_view FOR EACH ROW BEGIN UPDATE dept SET location=:new.location WHERE dept_name=:old.dept_name; END; / Code Explanation
Output Trigger Created Step 6) Update of view after instead-of trigger. Now the error will not come as the “instead of trigger” will handle the update operation of this complex view. And when the code has executed the location of employee XXX will be updated to “France” from “Japan.” BEGIN UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX'; COMMIT; END; / SELECT * FROM guru99_emp_view; Code Explanation:
Output: PL/SQL procedure successfully completed
Compound TriggerThe Compound trigger is a trigger that allows you to specify actions for each of four timing points in the single trigger body. The four different timing point it supports is as below.
It provides the facility to combine the actions for different timing into the same trigger. CREATE [ OR REPLACE ] TRIGGER Syntax Explanation:
Example 1: In this example, we are going to create a trigger to auto-populate the salary column with the default value 5000. CREATE TRIGGER emp_trig FOR INSERT ON emp COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :new.salary:=5000; END BEFORE EACH ROW; END emp_trig; / BEGIN INSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30); COMMIT; END; / SELECT * FROM emp WHERE emp_no=1004; Code Explanation:
Output: Trigger created PL/SQL procedure successfully completed.
Enabling and Disabling Triggers Triggers can be enabled or disabled. To enable or disable the trigger, an ALTER (DDL) statement needs to be given for the trigger that disable or enable it. Below are the syntax for enabling/disabling the triggers. ALTER TRIGGER Syntax Explanation:
SummaryIn this chapter, we have learnt about PL/SQL triggers and their advantages. We have also learnt the different classifications and discussed INSTEAD OF trigger and COMPOUND trigger. Which of the following code is executed automatically when there is an event occurs in the database?Trigger: A trigger is a stored procedure in database which automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.
Which is used to define code that is executed fired when certain actions or event occur cursors triggers?ANSWER: Trigger
The stored programs which execute automatically or when some events occur are known as triggers.
What is trigger explain?A trigger defines a set of actions that are performed in response to an insert, update, or delete operation on a specified table. When such an SQL operation is executed, the trigger is said to have been activated. Triggers are optional and are defined using the CREATE TRIGGER statement.
What is trigger and stored procedure?trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete) Stored procedures are a pieces of the code in written in PL/SQL to do some specific task. 2. Running Methodology. It can execute automatically based on the events.
|