Cursors and Trigger in SQL

In SQL, cursors are used to retrieve and manipulate data row by row, typically within stored procedures or functions:-

While cursors can be powerful tools so they should be use judiciously as they can have performance implications and especially with the large datasets.

There are different types of SQL cursors are there :-

1. Forward Only

2. Static

3. KeySet

4. Dynamic

Here's a simple practical example of how to create and use a cursor in SQL

--Create a Cursor

DECLARE EMP_CURSOR SELECT FIRSTNAME, SALARY FROM EMP

--Declare variables to store values fetched from the cursor

DECLARE @FIRSTNAME NVARCHAR(50);

DECLARE @SALARY DECIMAL(10,2);

--Open the Cursor

OPEN EMP_CURSOR

--Fetch the first row into variable

FETCH NEXT FROM EMP_CURSOR INTO @FIRSTNAME, @SALARY .;

--Loop through the cursor and print the results

WHILE @@FETCH_STATUS = 0

BEGIN

--Print FirstName and Salary

PRINT 'NAME:' + @FIRSTNAME + ' , SALARY ' + CAST(@SALARY AS NVARCHAR(20));

--Fetch the next row

FETCH NEXT FROM EMP_CURSOR INTO @FIRSTNAME, @SALARY .

END

--Close the cursor

CLOSE EMP_CURSOR

--Deallocate the cursor

DEALLOCATE THE CURSOR

Notes :- Remember , cursors should be used sparingly , especially in high performance enfironments ,as they can be less efficient compared to set based operations.

{The code example that I wrote on the above part that's for the SQL part but for the oracle the code will be different}

TRIGGER:-

A Trigger is a type of database object in SQL that automatically executes a set of actions in response to certain events occurring in a database.

These events occurring in a database. These events can include operations like INSERT, UPDATE, DELETE or even database startup or shutdown.

Triggers are often used to enforce data integrity rules, automatic tasks or log changes in the database..

The practical example of it:-
It's like First create a table of main and then create another table and named it Backup and after that insert some values inside the main table and after that write this query to create trigger for the table that you want to create.

Create or replace trigger t1 before delete on main for each row begin insert into backup values(:old.id, :old.salary);

The old part which I wrote in the query that old is coming from the main table id and salary and in the output we get the message of Trigger created.

And if Write the data like Select * from backup then the output we'll get is No Data Found.

And,
After that Suppose we wrote the query for the delete like delete from main where id = 1;

Then the output we'll get is :- 1 row deleted

But when we check our backup table for the query like Is there any query which is present inside the main table then we'll get other data from that table which is present there but not the data which got deleted .

But when we check our trigger by writing this query like :-
Select * from backup

Then we'll get the deleted data which we deleted from the main table not the remaining ones.