1. Cursor in PL/SQL :
A cursor can be basically referred to as a pointer to the context area.Context area is a memory area that is created by Oracle when SQL statement is processed.The cursor is thus responsible for holding the rows that have been returned by a SQL statement.Thus the PL/SQL controls the context area by the help of cursor.An Active set is basically the set of rows that the cursor holds. The cursor can be of two types: Implicit Cursor, and Explicit Cursor.
Advantages of Cursor:
- They are helpful in performing the row by row processing and also row wise validation on each row.
- Better concurrency control can be achieved by using cursors.
- Cursors are faster than while loops.
Disadvantages of Cursor:
- They use more resources each time and thus may result in network round trip.
- More number of network round trips can degrade the performance and reduce the speed.
2. Trigger in PL/SQL :
A Trigger is basically a program which gets automatically executed in response to some events such as modification in the database.Some of the events for their execution are DDL statement, DML statement or any Database operation.Triggers are thus stored within the database and come into action when specific conditions match.Hence, they can be defined on any schema, table, view etc. There are six types of triggers: BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, BEFORE DELETE, and AFTER DELETE.
Advantages of Trigger:
- They are helpful in keeping the track of all the changes within the database.
- They also help in maintaining the integrity constraints.
Disadvantages of Trigger:
- They are very difficult to view which makes the debugging also difficult.
- Too much use of the triggers or writing complex codes within a trigger can slow down the performance.
Difference between Cursor and Trigger:
|1.||It is a pointer which is used to control the context area and also to go through the records in the database.||It is a program which gets executed in response to occurrence of some events.|
|2.||A cursor can be created within a trigger by writing the declare statement inside the trigger.||A trigger cannot be created within a cursor.||3.||It gets created in response to execution of SQL statement thus it is not previously stored.||It is a previously stored program.||4.||The main function of the cursor is retrieval of rows from the result set one at a time (row by row).||The main function of trigger is to maintain the integrity of the database.||5.||A cursor is activated and thus created in response to any SQL statement.||A trigger is executed in response to a DDL statement, DML statement or any database operation.||6.||The main disadvantage of cursor is that it uses more resources each time and thus results in network round trip.||The main disadvantage of trigger is that they are hard to view which makes the debugging really difficult.|
Don’t stop now and take your learning to the next level. Learn all the important concepts of Data Structures and Algorithms with the help of the most trusted course: DSA Self Paced. Become industry ready at a student-friendly price.
- Difference between Trigger and Procedure in DBMS
- Difference between 1NF and 2NF in DBMS
- Difference between 2NF and 3NF in DBMS
- Difference between DDL and DML in DBMS
- Difference between Database and DBMS
- Difference between RDBMS and DBMS
- Difference between 3NF and BCNF in DBMS
- Difference between Schema and Instance in DBMS
- Difference between Generalization and Specialization in DBMS
- Difference between Classification and Clustering in DBMS
- Difference between OLAP and OLTP in DBMS
- Difference between Selection and Projection in DBMS
- Difference between File System and DBMS
- Difference between Client /Server and Distributed DBMS
- SQL Trigger | Student Database
- SQL Trigger | Book Management Database
- Main difference between Timestamp protocol and Thomos write rule in DBMS
- Check if Table, View, Trigger, etc present in Oracle
- What is Cursor in SQL ?
- Difference between Row oriented and Column oriented data stores in DBMS
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.