Triggers vs Cursors
In a database, a trigger is a procedure (code segment) that is executed automatically when some specific events occur in a table/view. Among its other uses, triggers are mainly used for maintaining integrity in a database. A cursor is a control structure used in databases to go through the database records. It is very similar to the iterator provided by many programming languages.
What are Triggers?
A trigger is a procedure (code segment) that is executed automatically when some specific events occur in a table/view of a database. Among its other uses, triggers are mainly used for maintaining integrity in a database. Triggers are also used for enforcing business rules, auditing changes in the database and replicating data. Most common triggers are Data Manipulation Language (DML) triggers that are triggered when data is manipulated. Some database systems support non-data triggers, which are triggered when Data Definition Language (DDL) events occur. Some examples are triggers that are fired when tables are created, during commit or rollback operations occur, etc. These triggers can be especially used for auditing. Oracle database system supports schema level triggers (i.e. triggers fired when database schemas are modified) such as After Creation, Before Alter, After Alter, Before Drop, After Drop, etc. The four main types of triggers supported by Oracle are Row Level triggers, Column Level triggers, Each Row Type triggers and For Each Statement Type triggers.
What are Cursors?
A cursor is a control structure used in databases to go through the database records. It is very similar to the iterator provided by many programming languages. In addition to traversing through records in a database, cursors also facilitate data retrieval, adding and deleting records. By defining the correct way, cursors can also be used to traverse backwards. When a SQL query returns a set of rows, those are actually processed using cursors. A cursor needs to be declared and assigned a name, before it could be used. Then the cursor needs to be opened using the OPEN command. This operation would place the cursor just before the first row of the result set of records. Then the cursor has to perform the FETCH operation to actually get a row of data in to the application. Finally, the cursor has to be closed using the CLOSE operation. Closed cursors can be opened again.
What is the difference between Triggers and Cursors?
A trigger is a procedure (code segment) that is executed automatically when some specific events occur in a table/view of a database, while a cursor is a control structure used in databases to go through the database records. A cursor can be declared and used within a trigger. In such a situation, the declare statement would be inside the trigger. Then the scope of the cursor would be limited to that trigger. Within a trigger, if a cursor is declared on an inserted or a deleted table, such a cursor would not be accessible from a nested trigger. Once a trigger is completed, all the cursors created within the trigger will be de-allocated.