What Is Trigger And Types Of Trigger

Question

Hi friend I want to know what is trigger ? And Insted of trigger or After trigger in sql server. Thank you.

Satyapriya Nayak · Answer

Hi Ajay A set of PLSQL statements automatically executed when DML statements are performed and stored permanently in database. USES:- used to impose user defined restrictions or business rules on database table. Types of triggers A trigger defines a set of actions that are performed in response to an insert update or delete operation on a specified table. When such an SQL operation is executed the trigger is said to have been activated . Triggers are optional and are defined using the CREATE TRIGGER statement. Triggers can be used along with referential constraints and check constraints to enforce data integrity rules. Triggers can also be used to cause updates to other tables automatically generate or transform values for inserted or updated rows or invoke functions to perform tasks such as issuing alerts. The following types of triggers are supported: BEFORE triggers Run before an update or insert. Values that are being updated or inserted can be modified before the database is actually modified. You can use triggers that run before an update or insert in several ways: To check or modify values before they are actually updated or inserted in the database. This is useful if you need to transform data from the way the user sees it to some internal database format. To run other non-database operations coded in user-defined functions. BEFORE DELETE triggers Run before a delete. Checks values (a raises an error if necessary). AFTER triggers Run after an update insert or delete. You can use triggers that run after an update or insert in several ways: To update data in other tables. This capability is useful for maintaining relationships between data or in keeping audit trail information. To check against other data in the table or in other tables. This capability is useful to ensure data integrity when referential integrity constraints aren't appropriate or when table check constraints limit checking to the current table only. To run non-database operations coded in user-defined functions. This capability is useful when issuing alerts or to update information outside the database. INSTEAD OF triggers Describe how to perform insert update and delete operations against views that are too complex to support these operations natively. They allow applications to use a view as the sole interface for all SQL operations (insert delete update and select). BEFORE triggers By using triggers that run before an update or insert values that are being updated or inserted can be modified before the database is actually modified. These can be used to transform input from the application (user view of the data) to an internal database format where desired. AFTER triggers Triggers that run after an update insert or delete can be used in several ways. INSTEAD OF triggers INSTEAD OF triggers describe how to perform insert update and delete operations against complex views. INSTEAD OF triggers allow applications to use a view as the sole interface for all SQL operations (insert delete update and select). Please refer the below link http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.dbobj.doc%2Fdoc%2Fc0020224.html Thanks