Trigger in Database

Hi Rajesh,

A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database triggers enable DBAs (Data Base Administrators) to create additional relationships between separate databases. In other ways, a trigger can be defined to execute before or after an INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the event.

Triggers can be assigned to tables or views. However, although there are two types of triggers, INSTEAD OF and AFTER, only one type of trigger can be assigned to views. An INSTEAD OF trigger is the one that is usually associated with a view, and runs on an UPDATE action placed on that view. An AFTER trigger fires after a modification action has occurred.
From a performance viewpoint, the fewer the triggers, the better, as we will invoke less processes. Therefore, do not think that having one trigger per action to make things modular will not incur performance degradation. A trigger's main overhead is referencing either two specialist tables that exist in triggers – deleted and inserted or other tables for business rules. Modularizing triggers to make the whole process easier to understand will incur multiple references to these tables, and hence a greater overhead.

Please refer the below link

http://www.codeproject.com/Articles/38808/Overview-of-SQL-Server-database-Triggers

Thanks

hi,

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

DML, DDL, or logon trigger are avalable in SQL.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.

DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

 Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established. Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement.

http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx

hope this help.

Hello Rajesh,

Triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT or DELETE statement is issued against a table .we can define when to execute our trigger.....i mean to say....before/after Insert/update/delete
basic use of it is.....if we need to somthing before any of action happned i.e for e.g you want to clean table before any new insert

syntax is:-

CREATE TRIGGER Triggername
ON TableName
FOR INSERT
AS
       delete  from tablename

 

Thanks,
Pravin.

http://www.c-sharpcorner.com/uploadfile/shashikantray/create-delete-and-update-triggers-in-a-database/default.aspx
http://www.c-sharpcorner.com/UploadFile/john_charles/Triggersand_Active_Databases08232007152057PM/Triggersand_Active_Databases.aspx
http://www.c-sharpcorner.com/uploadfile/37db1d/creating-and-managing-triggers-in-sql-server-20052008/default.aspx