Downloaded 23 times
Uploaded byTharindu Weerasinghe
Triggers and Stored Procedures
The document discusses stored procedures and triggers in databases, noting that stored procedures are reusable SQL code stored on the database that can increase performance, while triggers automatically run SQL code in response to changes made to a database table, such as inserts, updates or deletes. Both stored procedures and triggers can help with tasks like validation, auditing, and increasing performance by reducing traffic between applications and databases.
More Related Content
Similar to Triggers and Stored Procedures
![Things to ponder before you start building [cooperate] software](https://cdn.slidesharecdn.com/ss_thumbnails/thingstoponderinbuildingcooperatesoftware-220128165216-thumbnail.jpg?width=640&height=640&fit=bounds)
Triggers and Stored Procedures
- 1. Stored Procedure &Triggers Tharindu Weerasinghe www.tharinduweerasinghe.com
- 2. l * A storedprocedure 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.
- 4. l Advantages of StoredProcedures * 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 StoredProcedures * 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, rnGood Road,rnNugegoda.',' ',' ','LKR 100,000.00','20141003','201410 10','19811123','Manager',0,'20150305',NULL,NULL), (2,'Ruwan Darshana','No.1, rnFine Road,rnNugegoda','ruwan@abc.com','777101010','200,000','20141003','2016 0115','19830216','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 (AdapterCN06TM1C72438 45K2D6CA01)','LP003',21),(9,'Laptop','NXG3HSG10E57E7600 (Adapter KP06503013548084D6PE03)','LP004',22),(11,'Laptop','NXMLZSG0F8DB3400 (AdapterF210821438001685)','LP005',16),(12,'Laptop','NXM24440F8563400 (AdapterADT KP06503013548F883EPE03)','LP006',27);
- 10.
- 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 * ASQL 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, update or 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','20161121','In Progress',' Enterprise Version of ERPC'), ('ERPC','ERPC PHP','20161002','InProgress','Aimed for Customers'),('TreeECOn','Tree ECOn','201502 13','OnHold',' 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 checkthe 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 = "TreeECOn"; $$$ 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 checkthe above Trigger? (contd.)
- 22. So what arethe 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.