Week 10:Triggers

Aim: Creation of insert trigger, delete trigger and update trigger.

Recommended Hardware / Software Requirements:

● Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or faster
processor with at least 64MB RAM and 100 MB free disk space.
● MySQL 5.6.1

Prerequisites: Student must know about the Relational Database SQL-Triggers.

MySQL>CREATE TABLE BUS(BUSNO VARCHAR(10) NOT NULL, SOURCE VARCHAR(10),

DESTINATION VARCHAR(10), CAPACITY INT(2), PRIMARY KEY(BUSNO));
MySQL>INSERT INTO BUS VALUES('AP123','HYD','CHENNAI','40');

CREATE TABLE BUS_AUDIT1(ID INT NOT NULL AUTO_INCREMENT, SOURCE

VARCHAR(10) NOT NULL, CHANGEDON DATETIME DEFAULT NULL, ACTION
VARCHAR(10) DEFAULT NULL, PRIMARY KEY(ID));
DELIMITER $$
CREATE TRIGGER BEFORE_BUS_UPDATE
BEFORE UPDATE ON BUS
FOR EACH ROW
BEGIN
INSERT INTO BUS_AUDIT1
SET action='update',
source=OLD.source,
changedon=NOW();
END$$

UPDATE
MySQL>UPDATE BUS SET SOURCE='KERALA' WHERE BUSNO='AP123'$$

S.No Source Changedon Action

1 Bangalore 2014:03:23 12:51:00 Insert
2 Kerala 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete
INSERT
CREATE TRIGGER BEFORE_BUS_INSERT
BEFORE INSERT ON BUS
FOR EACH ROW
BEGIN
INSERT INTO BUS_AUDIT1
SET action='Insert',
source=NEW.source,
changedon=NOW();
END$$
INSERT INTO BUS VALUES('AP789','VIZAG','HYDERABAD',30)$$

S.No Source Changedon Action

1 Bangalore 2014:03:23 12:51:00 Insert
2 Kerala 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete

CREATE TRIGGER BEFORE_BUS_DELETE

BEFORE DELETE ON BUS
FOR EACH ROW
BEGIN
DELETE FROM BUS_AUDIT1
SET action='Insert',
source=NEW.source,
changedon=NOW();
END$$
DELETE FROM BUS WHERE SOURCE=’HYDERABAD’$$
S.No Source Changedon Action
1 Bangalore 2014:03:23 12:51:00 Insert
2 Kerala 2014:03:25:12:56:00 Update
3 Mumbai 2014:04:26:12:59:02 Delete
Conclusion: The student is able to work on Triggers and create active database.

Viva-Voce

1. What is TRIGGER?

2. What is BEFORE Trigger?

3. What is AFTER Trigger?

4. What is the difference between BEFORE and AFTER?

5. What are ROW and Statement Triggers?

6. What is INSTEAD Triggers?

7. What are the types on Triggers?

8. It is possible to create Trigger on Views?

 Week 11: Procedures

Aim: Creation of stored procedures and execution of procedures and modification of

procedures.

Recommended Hardware / Software Requirements:

● Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or faster
processor with at least 64MB RAM and 100 MB free disk space.
● MySQL 5.6.1

Prerequisites: Student must know about the Relational Database SQL-Procedures

CREATE PROCEDURE BUS_PROC1()

BEGIN
SELECT * FROM BUS;
END$$
CALL BUS_PROC1()$$

BusNo Source Destination CoachType

AP123 Hyderabad Bangalore Volvo
AP234 Mumbai Hyderabad Semi-Sleeper

CREATE PROCEDURE SAMPLE2()

BEGIN
DECLARE X INT(3);
SET X=10;
SELECT X;
END$$

CALL SAMPLE2()$$
BusNo Source Destination CoachType
AP123 Hyderabad Bangalore Volvo
AP234 Mumbai Hyderabad Semi-Sleeper
CREATE PROCEDURE SIMPLE_PROC(OUT PARAM1 INT)
BEGIN
SELECT COUNT(*) INTO PARAM1 FROM BUS;
END$$
CALL SIMPLE_PROC(@a)$$
PARAM1
4

Conclusion: The Student is able to work on Stored Procedures.

Viva Voce

1. What is a stored procedure?

2. When would you use stored procedure or functions?
3. What are external procedures?
4. What is input parameter?
5. How to use stored procedures?
 Week 12: Cursors

Aim: Declare a cursor that defines a result set. Open the cursor to establish the result set. Fetch
the data into local variables as needed from the cursor, one row at a time. Close the cursor when
done.

Recommended Hardware / Software Requirements:

● Hardware Requirements: Intel Based desktop PC with minimum of 166 MHZ or faster
processor with at least 64MB RAM and 100 MB free disk space.
● MySQL 5.6.1

Prerequisites: Student must know about the Relational SQL-Cursors

Cursors
In MySQL, a cursor allows row-by-row processing of the result sets. A cursor is used for the
result set and returned from a query. By using a cursor, you can iterate, or by step through the
results of a query and perform certain operations on each row. The cursor allows you to iterate
through the result set and then perform the additional processing only on the rows that require
it.

In a cursor contains the data in a loop. Cursors may be different from SQL commands that
operate on all the rows in the returned by a query at one time.

There are some steps we have to follow, given below:

● Declare a cursor

● Open a cursor statement

● Fetch the cursor

● Close the cursor

1. Declaration of Cursor: To declare a cursor you must use the DECLARE statement. With
the help of the variables, conditions and handlers we need to declare a cursor before we can
use it. first of all we will give the cursor a name, this is how we will refer to it later in the
procedure. We can have more than one cursor in a single procedure so its necessary to give it
a name that will in some way tell us what its doing. We then need to specify the select statement
we want to associate with the cursor. The SQL statement can be any valid SQL statement and
it is possible to use a dynamic where clause using variable or parameters as we have seen
previously.

Syntax: DECLARE cursor_name CURSOR FOR select_statement;

2. Open a cursor statement: For open a cursor we must use the open statement. If we want to
fetch rows from it you must open the cursor.

Syntax: OPEN cursor_name;

3. Cursor fetch statement: When we have to retrieve the next row from the cursor and move
the cursor to next row then you need to fetch the cursor.
Syntax: FETCH cursor_name INTO var_name;

If any row exists, then the above statement fetches the next row and cursor pointer moves ahead
to the next row.

4. Cursor close statement: By this statement closed the open cursor.

Syntax: CLOSE_name;

By this statement we can close the previously opened cursor. If it is not closed explicitly then
a cursor is closed at the end of compound statement in which that was declared.

Example: In the following example first of all we have to declare the Cursor and select the all
records from Company table. And after opened the cursor we fetch the record one by one from
cursor. And then insert these records in Products table.

delimiter $$
CREATE PROCEDURE DemoCurs()
BEGIN
DECLARE d INT DEFAULT 0;
DECLARE c_id INT;
DECLARE c_name,c_address VARCHAR(20);
DECLARE cur CURSOR FOR SELECT * FROM company;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET d=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET d=1;
OPEN cur;
lbl: LOOP
IF d=1 THEN
LEAVE lbl;
END IF;
IF NOT d=1 THEN
FETCH cur INTO c_id,c_name,c_address;
INSERT INTO products VALUES(c_id,c_name,c_address);
END IF;
END LOOP;
CLOSE cur;
END$$
mysql> delimiter;
mysql> CALL DemoCurs();
Query OK, 1 row affected (0.12 sec)

mysql> SELECT * FROM Products;

C_id C_Name Address

12303 IBM Bangalore
83893 Infosys Hyderabad

Conclusion: The student is able to work on Cursors.

Viva Voce
1. What is a cursor?
2. What are the types of cursor?
3. What is the use of parameterized cursor?
4. What is the use of cursor variable?
5. What is a normal cursor?
6. What are explicit cursors attributes?