Introduction to Store Procedures
Firstly, one or more DML operations on Database are used to create Store Procedures. Secondly, it is a collection of SQL statements which accepts input in form of parameters. Moreover, store procedures may or may not return a value. They are reusable components and can perform various tasks.
Syntax of Store Procedures
CREATE PROCEDURE procedure_name(parameters)
BEGIN
//STATEMENTS;
END;
Now there are three different type of parameters which are as follows:
- IN
- OUT
- IN OUT
Firstly, IN is the default parameter for the procedure. It will get the value from the calling program.
Secondly, OUT will send the value to the calling program.
Thirdly, IN OUT can perform both the above operations.
For instance:
- DELIMITER //
- CREATE PROCEDURE set_counter( INOUT count int, IN inc int)
- BEGIN
- SET c = c+ inc;
- END
- //
- SET @counter = 1;
- CALL set_counter(@counter,1)
- SELECT @counter;
Hence, Store Procedures can have one or multiple parameters depending on the requirements.
Introduction to Triggers
Firstly, Triggers are the statement that executes automatically by the system as a side effect of a modification in the database. Moreover, there are two types of triggers. One is DDL Trigger and another is DML Trigger. DDL trigger will respond to DDL commands like create, alter, drop etc. In addition, DML trigger will respond to DML commands like update, delete etc.
Syntax of Triggers
CREATE TRIGGER trigger_name on database for create_table, alter_table as print ‘ you cannot create or alter table in the database’ rollback;
Difference between Store Procedures and Triggers
- Firstly, store procedure is explicitly call by the user using statements like EXECUTE or exec. Whereas, trigger is implicitly call whenever an event occurs like insert, update or delete.
- We can define procedure inside another procedure whereas we cannot define trigger inside another trigger.
- Transaction statements like COMMIT are allowed in Store Procedures but not in Triggers.
- We can return value in procedures but we cannot return any value in trigger.
Conclusion
In conclusion, we have learnt that one or more DML operations on Database are used to create Store Procedures. Moreover, it is a collection of SQL statements which accepts input in form of parameters. It can have one or multiple parameters depending on the requirements.
Triggers are the statement that executes automatically by the system as a side effect of a modification in the database. Moreover, there are two types of triggers. One is DDL Trigger and another is DML Trigger.
Lastly, we have seen the difference between store procedures and triggers. Procedure is explicitly call by the user using statements like EXECUTE or exec. Whereas, trigger is implicitly call whenever an event occurs like insert, update or delete.
