What is a Trigger in SQL Server?
A Trigger is a database object that is
attached to a table. In many aspects Trigger's are quite similar to a
Stored Procedure. That's why, triggers are sometimes referred to as a
'special kind of stored procedure'. The main difference between a
trigger and a stored procedure is that the former is attached to a
table and is only fired when an INSERT, UPDATE or DELETE occurs or
you can say a trigger is called automatically when a data
modification event is made against a table whereas a stored procedure
must be called explicitly.
Features Of Trigger:-
1. A batch of SQL code for an insert,
update or delete command is executed.
2. Business rules can be enforced on
modification of data.
Advantages of using SQL triggers:-
1. SQL triggers provide an alternative
way to check the integrity of data.
2. SQL triggers can catch errors in
business logic in the database layer.
3. SQL triggers provide an alternative
way to run scheduled tasks. By using SQL triggers, you don’t have
to wait to run the scheduled tasks because the triggers are invoked
automatically before or after a change is made to the data in
tables.
4. SQL triggers are very useful to
audit the changes of data in tables.
Disadvantages of using SQL
triggers:-
1. Does not accept arguments or
parameters.
2. Cannot perform commit or rollback.
3. Can cause table errors if poorly
written.
4. SQL triggers only can provide an
extended validation and they cannot replace all the validations. Some
simple validations have to be done in the application layer. For
example, you can validate user’s inputs in the client side by using
JavaScript or in the server side using server side scripting
languages such as JSP, PHP, ASP.NET, Perl, etc.
5. SQL triggers are invoked and
executed invisibly from client-applications therefore it is difficult
to figure out what happen in the database layer.
6. SQL triggers may increase the
overhead of the database server.
Types of Triggers:-
In Sql Server we can create four types
of triggers:- and Logon triggers.
1). Data Definition Language (DDL)
triggers: In SQL Server we can create triggers on DDL statements
like CREATE, ALTER, and DROP and certain system defined stored
procedures that perform DDL-like operations.
e.g. If you are going to execute the
CREATE LOGIN statement or the sp_addlogin stored procedure to create
login user, then both these can execute/fire a DDL trigger that you
can create on CREATE_LOGIN event of Sql Server.
We can use only FOR/AFTER clause in DDL
triggers not INSTEAD OF clause means we can make only After Trigger
on DDL statements.
DDL trigger can be used to observe and
control actions performed on the server, and to audit these
operations. DDL triggers can be used to manage administrator tasks
such as auditing and regulating database operations.
2). Data Manipulation Language (DML)
triggers: In SQL Server we can create triggers on DML statements
like INSERT, UPDATE, and DELETE and stored procedures that perform
DML-like operations. DML Triggers are of two types:
2.1). After Trigger (using
FOR/AFTER CLAUSE): This trigger fires after SQL Server finish the
execution of the action successfully that fired it.
e.g: If you insert record/row in a
table then the trigger related/associated with the insert event on
this table will fire only after the row passes all the constraints,
like as primary key constraint, and some rules. If the record/row
insertion fails, SQL Server will not fire the After Trigger.
2.2). Instead of Trigger (using
INSTEAD OF CLAUSE) : This type of trigger is fired before SQL
Server starts the execution of the action that fired it. This is
differ from the AFTER trigger, which fires after the action that
caused it to fire. We can have an INSTEAD OF insert/update/delete
trigger on a table that successfully executed but does not include
the actual insert/update/delete to the table.
e.g: If you insert record/row in a
table then the trigger related/associated with the insert event on
this table will fire before the row passes all the constraints, such
as primary key constraint and some rules. If the record/row insertion
fails, SQL Server will fire the Instead of Trigger.
3). CLR Triggers: These are
special type of triggers that based on the Common Language
Runtime(CLR) in .net framework. CLR integration of triggers has been
introduced with SQL Server 2008 and allows for triggers to be coded
in one of .NET languages like C#, Visual Basic and F#.
4). Logon Triggers: These are
special type of trigger that fire when LOGON event of Sql Server is
raised. This event is raised when a user session is being established
with Sql Server that is made after the authentication phase finishes,
but before the user session is actually established. Hence, all
messages that we define in the trigger such as error messages, will
be redirected to the SQL Server error log. Logon triggers do not fire
if authentication fails. We can use these triggers to audit and
control server sessions, such as to track login activity or limit the
number of sessions for a specific login.
Different modes of firing triggers?
After Trigger: An AFTER trigger fires
after SQL Server completes all actions successfully
Instead of Triggers: An INSTEAD OF
trigger causes SQL Server to execute the code in the trigger instead
of the operation that caused the trigger to fire.
Differences between triggers and
non-trigger stored procedures are (amongst others):
1). A non-trigger stored procedure is
like a program that has to be invoked explicitly either from code or
from a scheduler or from a batch job, etc. to do its work, whereas a
trigger is a special type of stored procedure that fires as a
response of an event rather than be directly executed by the user.
The event may be a change of data in a data column for example.
2). Triggers have types e.g DDL
Triggers and DML Triggers (of types: INSTEAD OF, For, and AFTER)
3). Non-Trigger Stored procedures can
reference any type of object, however, to reference a view, you must
use INSTEAD OF triggers.
4). In SQLServer, you can have any
number on non-trigger stored procedures but only 1 INSTEAD OF trigger
per table.
5). We can call a stored procedure from
front end (.asp files, .aspx files, .ascx files etc.) but we can't
call a trigger from these files.
6). Stored procedure can take the input
parameters, but we can't pass the parameters as an input to a
trigger.
Note:
* DML trigger can be composed by any
T-SQL statements, except CREATE DATABASE, ALTER DATABASE, DROP
DATABASE, LOAD DATABASE, LOAD LOG, RECONFIGURE, RESTORE DATABASE, and
RESTORE LOG statements.
* You cannot create triggers against
system tables or dynamic management views.
* Also, the TRUNCATE TABLE statement
does not fire a trigger because this operation does not log
individual row deletions.
* If you use the DATABASE option, the
scope of your DDL trigger will be the current database. If you use
the ALL SERVER option, the scope of your DDL triggers to the current
server.
PL/SQL Syntax to
write a trigger:
CREATE [OR REPLACE ]
TRIGGER trigger_name
{BEFORE | AFTER |
INSTEAD OF }
{INSERT [OR] |
UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS
o NEW AS n]
[FOR EACH ROW]
WHEN
(trigger_condition)
DECLARE
Declaration-statements
BEGIN
trigger_body
EXCEPTION
Exception-handling-statements
END;
# CREATE [OR
REPLACE] TRIGGER trigger_name: Creates or replaces an existing
trigger with the trigger_name.
# BEFORE specifies
the trigger fires before the triggering event is performed.
# AFTER specifies
the trigger fires after the triggering event is performed.
# INSTEAD OF
specifies the trigger fires instead of performing the triggering
event.The INSTEAD OF clause is used for creating trigger on a view.
# {INSERT [OR] |
UPDATE [OR] | DELETE}: This specifies the DML operation.
# [OF col_name]:
This specifies the column name that would be updated.
# ON table_name:
This specifies the name of the table associated with the trigger.
# REFERENCING OLD AS
o NEW AS n: This allows you to refer new and old values for various
DML statements, like INSERT, UPDATE, and DELETE.
# FOR EACH ROW: This
specifies a row level trigger, i.e., the trigger would be executed
for each row being affected. Otherwise the trigger will execute just
once when the SQL statement is executed, which is called a table
level trigger.
# WHEN
(trigger_condition): This provides a condition for rows for which the
trigger would fire. This clause is valid only for row level triggers.
# trigger_body
contains the SQL and PL/SQL statements that perform the trigger's
task.
NOTE:
There are two
types of triggers based on the which level it is triggered.
1) Row level
trigger: An event is triggered for each row upated, inserted or
deleted.
2) Statement level
trigger: An event is triggered for each sql statement executed.
e.g Create a trigger to send an email
to the Sales Manager when an order is entered whose priority is HIGH.
CREATE TABLE Orders
(Order_ID int IDENTITY,
Order_Priority varchar(10))
CREATE TRIGGER TR_Orders_INSERT
ON Orders
FOR INSERT
AS
IF (SELECT COUNT(*) FROM inserted
WHERE Order_Priority = 'High') = 1
BEGIN
PRINT 'Send an email'
END
How to know information about
triggers present?
USER_TRIGGERS is the data dictionary
view, which can be used to obtain information about any trigger.
DESC USER_TRIGGERS;
----------------------------------------------
NAME Type
----------------------------------------------
TRIGGER_NAME
VARCHAR2(30)
TRIGGER_TYPE
VARCHAR2(16)
TRIGGER_EVENT
VARCHAR2(75)
TABLE_OWNER
VARCHAR2(30)
BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME
VARCHAR2(30)
COLUMN_NAME
VARCHAR2(4000)
REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE
VARCHAR2(4000)
STATUS
VARCHAR2(8)
DESCRIPTION
VARCHAR2(4000)
ACTION_TYPE
VARCHAR2(11)
TRIGGER_BODY LONG
----------------------------------------------
How to drop a trigger
DROP TRIGGER Trigger_name
How to disable a trigger
DISABLE TRIGGER {schema name} trigger
name ON {object, database or ALL server}
Also Read:
Copyright © 2014 - ScrutinyByKHimaanshu
This is very good article.
ReplyDelete