Question: What
is a Stored Procedure?
Answer: Stored
Procedure is the collection of Structured Query Language (SQL) statements with
an assigned name stored in the database in compiled form. Stored Procedure can
be used over the network by several clients using different input data because
the stored procedure can accept input parameters. The stored procedure improved
performance, reducing network traffic.
Question: What is
Trigger in SQL Server?
Answer: Trigger
is the same as a procedure that is executed on the action when an event
(INSERT, DELETE, UPDATE, etc.) occurs. DBMS manage and store to trigger.
Triggers are commonly used to perform auditing action, to maintain the table
integrity in place of native constraints such as foreign key and check
constraints, and perform other DML processing. When a trigger is at work, the process can’t be completed until trigger completion because the trigger
operates under the scope of a transaction.
Question: Type of
trigger?
Answer: There
are two types of trigger DML and DDL.
1- DML
Trigger: DML trigger is a special type of stored procedure that
automatically takes effect when a data manipulation language (DML) event takes
place that affects the table or view defined in the trigger. DML events include
INSERT, UPDATE, or DELETE statements. There
are two types of DML trigger.
A) Instead
of Trigger: Instead of trigger override, the standards actions of the
triggering means fire in the place of the triggering action such as insert,
delete, or update. For example: when the value is updated in an hourly
wage column in a payroll table exceeds a specific value.
B) After
Trigger: This triggers fire after the execution of an action query.
After trigger fire for both DML and DDL statements.
2- DDL
Trigger: This type of trigger fired when an action occurs like Drop,
Create, and Alter Table DDL triggers are always after a trigger.
