Trigger in Database

Question

Hi.. How can we use Trigger in Database and define syntax and query?

Satyapriya Nayak · Answer

Hi Rajesh, A database trigger is a stored procedure that is invoked automatically when a predefined event occurs. Database trigger s enable DBAs (Data Base Administrators) to create additional relationships between separate database s. 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. Trigger s can be assigned to tables or views. However, although there are two types of trigger s, 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 trigger s, 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 trigger s – deleted and inserted or other tables for business rules. Modularizing trigger s 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

Jignesh Trivedi · Answer

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.

Pravin More · Answer

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.

Muralidharan Deenathayalan · Answer

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