Trigger and Procedure both are modules of advanced SQL. Both perform a specified task on their execution. In this article, we will discuss about trigger and procedure. So read on the full article to know what is Trigger, what is the procedure and the difference between Procedure and trigger in SQL.
What is a Trigger?
A trigger is similar to a procedure executed automatically on the occurrence of a particular event. As a procedure, the trigger does not require to be called openly. Triggers are created, to perform a task in response to the occurrence of a particular event.
A trigger can be invoked in response to the DDL statements (DELETE, INSERT, or UPDATE), or, to some database operations (SERVER ERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN) or DML statements (DELETE, INSERT, or UPDATE).
Components of Trigger
A trigger consists of 3 components as described below:
Event: Event is an occurrence of an incident that will cause the execution of the trigger. The trigger can be considered to execute in two possibilities. Either BEFORE an event occurs or it may be ordered to get executed AFTER the execution of an event.
Condition: It is an additional or optional part of the trigger. If not mentioned, the trigger will execute as a particular event occurs. If the condition is specified, then it will check the rules to conclude whether the trigger should be executed.
Action: Action is a set SQL statement. And it will be executed on the execution of the Trigger.
The format of creation of an event is described as below:–
- CREATE TRIGGER <Trigger_Name>
- BEFORE/AFTER <Events>
- CONDITION
- ACTION;
Note- Condition is optional here.
What is the Procedure?
The procedure can be considered as a program unit, created to perform a task. It is stored on the database. Procedures are invoked by the SQL statement whenever required. Procedures are like user-defined functions and these are defined by the developers. Procedures can be invoked using CALL or EXECUTE.
The procedures are useful in some following situations:
- If the procedure is required by several other applications, then it can be stored on the server & can be invoked by any application. It will decrease the effort of the repetition of the procedure from one database to another. And it also improves the modularity of the software.
- As the procedure is executing on the server, it will reduce data transfer and also reduce the communication cost.
- The procedures can be used to check the difficult constraints that are away from
- the power of the trigger.
Format of creating a procedure:
- CREATE PROCEDURE <procedure name> (<parameters>)
- RETURNS <return type>
- <local declarations>
- <procedure body> ;
The statement below defines the calling of the procedures.
CALL <procedure or function name> (<argument list>) ;
Difference between Triggers and Procedures
Trigger vs Procedures in SQL
| Features | Triggers | Procedures |
|
1. Basic |
On the occurrence of a specific event, Triggers are automatically executed. | Procedures can be executed whenever required.
|
| 2. Calling | Triggers cannot be called inside a procedure. | We can call a procedure inside a trigger. |
| 3. Return | it never return value on execution. | it may return value/s on execution. |
| 4. Parameter | We cannot pass parameters to triggers. |
We can pass parameters to procedures. |
Also check: Difference between RDBMS and Hadoop
Conclusion
The primary difference between Trigger and Procedure is that the Procedure is executed when it is explicitly invoked. And the Trigger executes automatically on occurrences of an event. Triggers are very useful. But they are avoided if there exist any alternative to them, as it increases data complication. Sometimes triggers are also substitutes by a suitable procedure. In this article we have read about the main difference between Triggers and Procedures. If you are having any doubt, ask me freely in the comment box.
