Exp.
No:5a TRIGGERS
AIM
To study and implement the concepts of triggers.
DEFINITION
A trigger is a statement that is executed automatically by the system as a side effect of a
modification to the database. The parts of a trigger are,
Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies the
table to which the trigger is associated.
Trigger body or trigger action: It is a PL/SQL block that is executed when The triggering
statement is used.
Trigger restriction: Restrictions on the trigger can be achieved The different uses of triggers are
as follows,
• To generate data automatically
• To enforce complex integrity constraints
• To customize complex securing authorizations
• To maintain the replicate table
• To audit data modifications
TRIGGERS - SYNTAX
CREATE [OR REPLACE] TRIGGER trigger_ nameBEFORE|AFTER
[INSERT,UPDATE,DELETE[COLUMNNAME..]
ON table_name
Referencing[OLDASOLD|NEWAS NEW]
FOREACHROW|FOREACHSTATEMENT [ WHEN Condition]
DECLARE
[declaration_section
variable declarations;constantdeclarations;
]
BEGIN
[executable_section
PL/SQLexecute/subprogram body
] EXCEPTION
[exception_section
PL/SQLExceptionblock ]
1. mysql> create database tr;
Query OK, 1 row affected (0.02 sec)
2. mysql> use tr;
Database changed
3. mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.06 sec)
4. mysql> CREATE TABLE test2(a2 INT);
Query OK, 0 rows affected (0.25 sec)
5. mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY
KEY);
Query OK, 0 rows affected (0.08 sec)
6. mysql> CREATE TABLE test4(
-> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> b4 INT DEFAULT 0
-> );
Query OK, 0 rows affected (0.06 sec)
7. mysql> delimiter |
8. mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO test2 SET a2 = NEW.a1;
-> DELETE FROM test3 WHERE a3 = NEW.a1;
-> UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
-> END;
-> |
Query OK, 0 rows affected (0.01 sec)
9. mysql> delimiter ;
10. mysql> INSERT INTO test3 (a3) VALUES
-> (NULL), (NULL), (NULL), (NULL), (NULL),
-> (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 10 rows affected (0.04 sec)
Records: 10 Duplicates: 0 Warnings: 0
11. mysql> INSERT INTO test4 (a4) VALUES
-> (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
12. mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0
13. mysql> SELECT * FROM test1;
14. mysql> SELECT * FROM test2;
15. mysql> SELECT * FROM test3;
16. mysql> SELECT * FROM test4;
RESULT
Thus the Triggers were created, executed and their respective outputs were verified.
Exp.No:5b PROCEDURES
AIM:
To write MYSQL programs that executes the concept of procedures.
DEFINITION:
A procedure or function is a logically grouped set of SQL and PL/SQL statements that
perform a specific task. They are essentially sub-programs.
Procedures and functions are made up of,
• Declarative part
• Executable part
• Optional exception handling part
These procedures and functions do not show the errors.
KEYWORDS AND THEIR PURPOSES REPLACE:
It recreates the procedure if it already exists.
PROCEDURE: It is the name of the procedure to be created.
ARGUMENT: It is the name of the argument to the procedure. Paranthesis can be omitted
if no arguments are present.
IN: Specifies that a value for the argument must be specified when calling the procedure
ie. used to pass values to a sub-program. This is the default parameter.
OUT: Specifies that the procedure passes a value for this argument back to it’s calling
environment after execution ie. used to return values to a caller of the sub-program.
INOUT: Specifies that a value for the argument must be specified when calling the
procedure and that procedure passes a value for this argument back to it’s calling
environment after execution. RETURN: It is the datatype of the function’s return value
because every function must return a value, this clause is required.
SYNTAX:
CREATE
[DEFINER = user]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
mysql> USE database_name;
Procedure without Parameter
Procedures with IN Parameter
DELIMITER &&
CREATE PROCEDURE get_student (IN var1 INT)
BEGIN
SELECT * FROM student_info LIMIT var1;
SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END &&
DELIMITER ;
Procedures with OUT Parameter
DELIMITER &&
CREATE PROCEDURE display_max_mark (OUT highestmark INT)
BEGIN
SELECT MAX(marks) INTO highestmark FROM student_info;
END &&
DELIMITER ;
Procedures with INOUT Parameter
DELIMITER &&
CREATE PROCEDURE display_marks (INOUT var1 INT)
BEGIN
SELECT marks INTO var1 FROM student_info WHERE stud_id = var1;
END &&
DELIMITER ;
Result:
The MYSQL queries to create procedures and functions were executed and
their respective outputs were verified.
