What is trigger in databaseA trigger is a special kind of stored procedure that automatically executes when an event occurs in the database. DML triggers execute when a user tries to modify data in a database table. A trigger can be executed on INSERT, UPDATE, or DELETE statements on a table or view. Triggers that are fired before any action performed are called Before Triggers and trigger that are fired after the action has been performed are called After Triggers.
Types of Triggers
- DDL Triggers - This type of trigger are fired against DDL statements like Drop Table, Create Table, Or Alter Table. DDL Triggers are always AFTER triggers.
- DML Triggers - This type of triggers are fired against DML statements like INSERT, UPDATE, DELETE. DML Trigger can be Instead Of, Before and After. Instead of trigger are fired in place of the triggering action such as an insert,update, or delete.
Below is the example of a simple trigger:
CREATE OR REPLACE TRIGGER INCREMENT_SALARY BEFORE DELETE ON CUSTOMERS FOR EACH ROW BEGIN UPDATE CUSTOMERS SET SALARY = NEW.SALARY; END
What is stored procedure in databaseA stored procedure is a set of SQL statements with an assigned name that's stored in the database compiled into a single execution plan. It may or may not return result. Mainly, those SQL statements and logic that need to perform a commonly performed task can be designed and coded in a stored procedure.
Below is the example of a simple stored procedure:
CREATE PROCEDURE USP_GET_ADDRESS AS BEGIN SELECT * FROM AdventureWorks.Person.Address END
Below are the points that differentiate a stored procedure from a trigger:
|The stored procedures is normally used to performing user specified tasks. It can have the parameters and return multiple results set.||The Triggers is normally used for auditing work. It can be used to trace the activities of the table events.|
|The procedures can have the input and output parameters. The datatypes can be built-in datatypes or user defined data types.||The Triggers cannot have any parameters.|
|The stored procedures can be run independently. It stores as a database object. It can be called from an external application.||The triggers executes based on table events. The DML triggers are get executed based on the table events defined on the particular table.|
|The stored procedures cannot call the triggers directly. But when we do the DML operations on the table within in a stored procedure, and if the corresponding table has trigger then trigger will be executed if the specified event associated with trigger is executed.||The triggers can call stored procedures.|
Please feel free to post any comment or question.