Trigger and Procedure are Component of Advanced SQL. Trigger and Procedure both perform a specified task on their execution. The fundamental difference between Trigger and Procedure is that the Trigger executes automatically on occurrences of an event whereas, the Procedure is executed when it is explicitly invoked.
Let us discuss some more differences between Trigger and Procedure with the help of a comparison chart shown below.
Content: Trigger Vs Procedure
|Basis for Comparison
|They are automatically executed on occurrence of specified event.
|They can be executed whenever required.
|Triggers can't be called inside a procedure.
|But, you can call a procedure inside a trigger.
|We can not pass parameters to triggers.
|We can pass parameters to procedures.
|Trigger never return value on execution.
|Procedure may return value/s on execution.
Definition of Trigger
The trigger is like a procedure that gets executed automatically on the occurrence of a specified event. Like the procedure, the trigger does not need to be called explicitly. Triggers are created, to perform some task in response to the occurrence of some specified event.
The trigger can be invoked in response to the DDL statements (DELETE, INSERT, or UPDATE), or DML statements (DELETE, INSERT, or UPDATE) or, to some database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
The trigger consists of three component as discussed below:
- Event: Event is the occurrence of some incident that will cause execution of the trigger. The trigger can be ordered to execute either BEFORE an event occurs or it may be ordered to get executed AFTER the execution of an event.
- Condition: It is an optional part of the trigger. If not mentioned trigger will execute as the specified event occurs. If the condition is specified, then it will check the rules to determine whether the trigger should be executed.
- Action: Action is a set SQL statements that will be executed on the execution of the Trigger.
The general form of creation of an event is discussed below:
CREATE TRIGGER <Trigger_Name> BEFORE/AFTER <Events> CONDITION ACTION;
Here, Condition is optional.
Definition of Procedures
The procedure can be taken as a program unit, created to perform some task and it is stored on the database. They are invoked by the SQL statement whenever required. Procedures are like user defined functions that are defined by the developers. Procedures can be invoked using CALL or EXECUTE.
The procedures are useful in following situations:
- If the procedure is required by several other application, then it can be stored on the server so that they can be invoked by any application. It will reduce the effort of duplication of the procedure from one database to another and also improves 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 complex constraints that are beyond the power of trigger.
Let us discuss the general form of creating a procedure:
CREATE PROCEDURE <procedure name> (<parameters>) RETURNS <return type> <local declarations> <procedure body> ;
Here, the parameters and the local declarations are optional. They are mentioned only when they are required. The statement below describes the calling of the procedures.
CALL <procedure or function name> (<argument list>) ;
Key Differences Between Trigger and Procedure
- The primary difference between trigger and procedure is that a trigger is a statement that gets invoked automatically when an event has occurred. On the other hand, the procedure is invoked whenever it is required.
- One can define procedure inside a trigger. But, a trigger is never defined inside a procedure as the trigger has to be invoked automatically on the occurrence of any event.
- We can pass parameters to procedures, but we can not pass parameters to trigger as it is not invoked by us.
- A procedure can return parameter values or code but, a trigger can not.
Triggers are useful, but they are avoided if there exist any alternative to them, as it increases data complexity. Sometimes triggers are also substitutes by an appropriate procedure.