Trigger: required for writing a trigger. 1. Firstly we

Trigger:

Trigger is same like a stored procedures where we can write
SQL code and save it which gets executed automatically whenever an event takes
place on the associated database.  We
cannot call triggers directly. There are 2 steps that are required for writing
a trigger.

We Will Write a Custom Essay Specifically
For You For Only $13.90/page!


order now

1.      
Firstly we must mention when the trigger has to
be executed like. Such that this is considered as an event and checks the
trigger and whether it satisfies the given condition for which the trigger has
to execute.

2.      
Now we have to mention the actions that has to
take place when the trigger is executed.

Uses of Triggers:

1.      
Triggers are used to maintain the complex
integrity constraints which are not possible at the time of table creation.

2.      
Automatically signalling the other programs that
an action needs to be take place when changes are made to a table.

3.      
If we want to implement any particular business
rules   then triggers are used in that
case.

 

Types of Triggers:

1.      
DML Triggers

2.      
DDL Triggers

3.      
Logon Triggers

DML triggers:

DML triggers will be executed automatically whenever a data
manipulation operations (insertion, deletion, updating) take place on the table
or view which is mentioned in the trigger. DML triggers are classified into two
types.

1.      
After Trigger

2.      
INSTEAD OF trigger

After Trigger: These types of triggers are fired only after
the execution of DML operations and before the committing the data into the
database.  We cannot apply after trigger
on views.

For example: Create an after trigger for insert operation.  For table employee which has columns empno,
empfirstname, emplastname, deptno, experience.

USE testing

GO

 

CREATE TRIGGER inserttrigeer

   ON  employee

   AFTER  INSERT

AS

declare

              @ID
int

       set @ID = (select experience from inserted)

BEGIN

if(@ID 5)

begin

       print ‘ Experience is greater than 5′

end

 

END

GO

Then if we give an insert statement

insert into employee values (4,’abc’,’kil’,’p2′,8)

 so before the insert
statement is executed  the instead of
trigger for insert is raised and it will execute in place of insert operation
and gives output as experience is greater than 5 and when you check the table
the row will not inserted.

Uses of DML Triggers:

1.      
DML triggers are same as constraints which
enforce the data integrity.

2.      
A normal constraint in the SQL gives a standard
system error message when an issue occurs. In order to customize the message
and to handle more complex error-handling DML triggers are used.

3.      
To perform multiple actions for the same event
we can create more than one trigger using DML triggers.

DDL Triggers:

These type of triggers are fired when any of the DDL
statements are executed i.e.; DDL events occurred like when creation,
alteration, granting permission, denning permissions, dropping, Revoke
permissions.

The scope of the DDL triggers:

We can create the DDL triggers in a particular database or
at the server level. So when you create a DDL trigger we must specify whether
it is database level or server level by ON DATABASE clause or ON ALL SERVER
clause respective.  Database level means
when creating a table, procedure, functions and altering a table, procedure and
function in a database. Server level means when creating a login, database,
altering the database, login and dropping the login, granting the server etc.

Unlike the DML triggers DDL triggers doesn’t contain the
INSTEAD OF trigger. DDL trigger will not be executed if an event occurs such
that it has an impact on global temporary tables and procedures.

Uses of DDL Triggers:

1.      
By using DDL triggers we can place restrictions
on a user’s who are going to attempt to create, alter, and drop the database
objects.

2.      
Used to record the changes that take place in
the database schema.

Example:

Create a database scope DDL trigger

use testing

GO

CREATE TRIGGER droptesting

ON DATABASE

FOR   DROP_TABLE

AS

PRINT ‘table cannot be
dropped’

ROLLBACK

 

Now when we drop the table then the trigger will fire and
print the message table cannot be dropped and then drop command will rollback
and it doesn’t executed. 

x

Hi!
I'm Homer!

Would you like to get a custom essay? How about receiving a customized one?

Check it out