Triggers and Stored Procedures

Aimed at the 3rd year undergraduates of the University of Vocational Technology, Sri Lanka, 2018/19

1. Stored Procedure & Triggers
Tharindu Weerasinghe
www.tharinduweerasinghe.com

2. l

* A stored procedure is a segment of declarative SQL statements stored
inside the database catalog. (directory of information in database).
* A stored procedure can be invoked by triggers, other stored procedures,
and programming languages in the likes of Java, Python, PHP.
Stored Procedures

3. Stored Procedures (contd.)

4. l

Advantages of Stored Procedures
* Increase the performance of applications.
* Reduce traffic between the application and database
server.
* Reusable and transparent to all applications that access
the particular database.
* Secure.

5. l

Disadvantages of Stored Procedures
* If there are many procedures, the memory consumption
will be high and the overall system might stuck!
* Can't implement complex business logics.
* Not easy to maintain.
* Hard to debug the stored procedures.

6. A sample – Initial Setup
I aussume you all have a mysql user in your lab.
mysql -u username -p
If you know the root password let's login as root
mysql-u root -p
 
CREATE DATABASE dbname;
USE dbname;
These things you should know by now as you should have done
these in your previous course. So our dbname is dblessons;

7. A sample – Initial Setup
I aussume you all have a mysql user in your lab.
mysql -u username -p
If you know the root password let's login as root
mysql-u root -p
 
CREATE DATABASE dbname;
USE dbname;
These things you should know by now as you should have done
these in your previous course. So our dbname is dblessons;

8. A sample – Create Tables
CREATE TABLE `inventory` (
  `seqid` int(20) NOT NULL 
AUTO_INCREMENT,
  `itemtype` varchar(300) NOT NULL,
  `itemname` text NOT NULL,
  `itemid` varchar(20) NOT NULL,
  `usedby` int(10) DEFAULT NULL,
  PRIMARY KEY (`seqid`),
  UNIQUE KEY `itemid` (`itemid`)
) ENGINE=InnoDB AUTO_INCREMENT=123 
DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = 
@saved_cs_client */;
CREATE TABLE `employees` (
  `empid` int(10) unsigned NOT NULL,
  `name` varchar(300) NOT NULL,
  `homeaddress` text NOT NULL,
  `email` varchar(200) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `salary` varchar(20) NOT NULL,
  `joineddate` varchar(25) NOT NULL,
  `lastincrementdate` varchar(25) 
NOT NULL,
  `dateofbirth` varchar(25) NOT 
NULL,
  `designation` varchar(30) NOT 
NULL,
  `leaves` int(11) DEFAULT NULL,
  `resigneddate` varchar(25) DEFAULT 
NULL,
  `expertise` varchar(75) DEFAULT 
NULL,
  `nic` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
) ENGINE=InnoDB DEFAULT 
CHARSET=latin1;

9. A sample – Insert Some Records
INSERT INTO `employees` VALUES(1,'Nimal Shantha','No.1, \r\nGood 
Road,\r\nNugegoda.',' ­',' ­ ','LKR 100,000.00','2014­10­03','2014­10­
10','1981­11­23','Manager',0,'2015­03­05',NULL,NULL),
(2,'Ruwan Darshana','No.1, \r\nFine 
Road,\r\nNugegoda','[email protected]','777101010','200,000','2014­10­03','2016­
01­15','1983­02­16','Head of Operations',24,'','Management, C++, Core Java, 
Python, Node JS','840141615V');
INSERT INTO `inventory` VALUES (1,'Laptop','NXMLZ0F8BE3400 (Adapter­
F210821437003201)','LP001',23),(2,'Laptop','NXMCAS01010847600 (Adapter­
F5TB0429143195)','LP002',20),(4,'Laptop','D499B12 (Adapter­CN­06TM1C­72438­
45K­2D6C­A01)','LP003',21),(9,'Laptop','NXG3HSG10E57E7600 (Adapter­
KP06503013548084D6PE03)','LP004',22),(11,'Laptop','NXMLZSG0F8DB3400 
(Adapter­F210821438001685)','LP005',16),(12,'Laptop','NXM24440F8563400 
(Adapter­ADT KP06503013548F883EPE03)','LP006',27);

10. DELIMITER <any character(s) you like, e.g. *>  
CREATE PROCEDURE item_type (IN item 
VARCHAR(20))
 
BEGIN   SELECT *  FROM inventory WHERE 
itemtype  = item; 
END <the above delimiter, which is *>  
A sample – Create a Procedure
(MySQL, MariaDB)

11. A sample – Call and See a Procedure
(MySQL, MariaDB)
Calling the created procedure:
CALL item_type(“Laptop”); 
<DELIMITER value declared above, which 
is *>
See the created procedure:
SHOW CREATE PROCEDURE 
item_type; <DELIMITER value 
declared above, which is *>

12. A sample – Complex Procedure –
Try something like these!
CREATE PROCEDURE
  Withdraw                             /* 
Routine name */
  (parameter_amount DECIMAL(6,2),     /* 
Parameter list */
  parameter_teller_id INTEGER,
  parameter_customer_id INTEGER)
  MODIFIES SQL DATA                   /* 
Data access clause */
  BEGIN                        /* Routine 
body */
    UPDATE Customers
        SET balance = balance ­ 
parameter_amount
        WHERE customer_id = 
parameter_customer_id;
    UPDATE Tellers
        SET cash_on_hand = cash_on_hand + 
parameter_amount
        WHERE teller_id = 
parameter_teller_id;
    INSERT INTO Transactions VALUES (
        parameter_customer_id,
        parameter_teller_id,
        parameter_amount);
  END;

13. Database Triggers
* A SQL trigger is a set of SQL statements
stored in the database catalog.
* A SQL trigger is run (or we say fired)
whenever an associated event
(with a) table occurs
e.g.,
 
insert, updateor
   
delete.

14. l

Advantages of Triggers
* Can check errors in the business logic in the db layer.
* Provide an alternative way to check data integrity.
* Useful to audit the changes of data in tables.

15. l

Disdvantages of Triggers
* Triggers can only provide an extended validation and they cannot do all
the validations. Some basic validations have to be done in the application
layer.
e.g. you can validate user’s inputs in the client side by using JavaScript or on the
server side using server-side scripting languages such as JSP, PHP, ASP.NET, Perl.
* May increase the overhead of the db.
* Triggers are invoked and executed invisible from the client applications,
therefore, it is difficult to figure out what happens in the database layer.

16. A sample – Trigger, related to a db log
(first create 2 tables)
CREATE TABLE `projects` (
  `projid` varchar(20) NOT NULL,
  `projname` varchar(30) NOT NULL,
  `starteddate` varchar(25) NOT NULL,
  `currentstatus` varchar(25) NOT NULL,
  `projinfo` varchar(300) DEFAULT NULL,
  PRIMARY KEY (`projid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE project_audit (
    id INT AUTO_INCREMENT PRIMARY 
KEY,
    projid VARCHAR(50) NOT NULL,
    projname VARCHAR(50) NOT NULL,
    currentstatus  VARCHAR(50)NOT 
NULL,
    action VARCHAR(50) DEFAULT 
NULL,
    changedat DATETIME DEFAULT 
NULL
);

17. A sample – Now Create the Trigger
DELIMITER $$$
CREATE TRIGGER after_project_update 
    AFTER UPDATE ON projects
    FOR EACH ROW 
BEGIN
    INSERT INTO project_audit
    SET action = 'update',
     projid = OLD.projid,
     projname = OLD.projname,
     changedat = NOW(); 
END $$$
DELIMITER;
This will list down all triggers you have created:
SHOW TRIGGERS; $$$ 

18. A sample – Trigger, related to a db log
(secondly enter some values to the projects table)
LOCK TABLES `projects` WRITE;
/*!40000 ALTER TABLE `projects` DISABLE KEYS */;
INSERT INTO `projects` VALUES
 ('Centralized','ERPCentralized','2016­11­21','In­
Progress','  Enterprise Version of ERPC'),
('ERPC','ERPC PHP','2016­10­02','In­Progress','Aimed 
for Customers'),('Tree­ECOn','Tree ECOn','2015­02­
13','On­Hold','  Tree STB ­ ECO');
/*!40000 ALTER TABLE `projects` ENABLE KEYS */;
UNLOCK TABLES;

19. A sample – Now Create the Trigger
DELIMITER $$$
CREATE TRIGGER after_project_update 
    AFTER UPDATE ON projects
    FOR EACH ROW 
BEGIN
    INSERT INTO project_audit
    SET action = 'update',
     projid = OLD.projid,
     projname = OLD.projname,
     changedat = NOW(); 
END $$$
DELIMITER;
This will list down all triggers you have created:
SHOW TRIGGERS; $$$ 

20. l

How to check the above Trigger?
We created a trigger that will execute after an update.
Hence we need to update the project table first.
UPDATE projects SET projname 
= "Fruit ECOn" WHERE projid = 
"Tree­ECOn"; $$$
Remember our delimiter this time is, $$$
Now, if you check your project_audit table then you will
see a new record being added.

21. l

How to check the above Trigger?
(contd.)

22. So what are the triggering options?
BEFORE INSERT – activated before data is inserted into the table.
AFTER INSERT – activated after data is inserted into the table.
BEFORE UPDATE – activated before data in the table is updated.
AFTER UPDATE – activated after data in the table is updated.
BEFORE DELETE – activated before data is removed from the table.
AFTER DELETE – activated after data is removed from the table.
Do more examples guys! Play with these! Enjoy triggering!

23. Gratitude
References:
http://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx
https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html
http://www.mysqltutorial.org/sql-triggers.aspx
https://searchsqlserver.techtarget.com/definition/catalog
http://www.mysqltutorial.org/sql-triggers.aspx
https://mariadb.com/kb/en/library/stored-procedure-overview/
http://www.mysqltutorial.org/create-the-first-trigger-in-mysql.aspx
http://www.mysqltutorial.org/mysql-trigger-implementation.aspx