How to create & Restore Triggers in MS SQL server

Trigger is a special type of stored procedure that automatically execute when an event occurs in the Microsoft SQL server database. A database administrator can create triggers in the MS SQL server 2005 database engine with the help of T-SQL statement. Users can create more than one trigger for the specific statement in the SQL server. There are three of triggers occur in the SQL server database, which are given below:

1. Data Manipulation Language Trigger
2. Data Definition Language Trigger
3. Logon Trigger

Data manipulation language trigger: It is also known as DML trigger. This trigger occurs when a database administrator tries to modify data through data manipulation language event. Insert, update and delete are the data manipulation language events. These all events are used to modify the data in the table of SQL server.

How to create DML trigger: You can create a DML trigger with the help of transact SQL statement. The syntax is as follows:

CREATE TRIGGER [ schema_name . ]trigger_name

ON { table | view }

[ WITH [ ,...n ] ]

{ FOR | AFTER | INSTEAD OF }

{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }

[ WITH APPEND ]

[ NOT FOR REPLICATION ]

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME }

::=

    [ ENCRYPTION ]

    [ EXECUTE AS Clause ]

::=

    assembly_name.class_name.method_name

Data definition language trigger: It is also known as DDL triggers and this type of trigger occurs when a database administrator tries to create database tables through data definition language events. Some data definition language events are create, alter, drop, and many more.

How to create an DDL trigger: An DDL trigger can be created by the below syntax.

CREATE TRIGGER trigger_name

ON { ALL SERVER | DATABASE }

[ WITH [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

::=

    [ ENCRYPTION ]

    [ EXECUTE AS Clause ]

::=

    assembly_name.class_name.method_name

Logon trigger: It occurs in response to logon events.

CREATE TRIGGER trigger_name

ON ALL SERVER

[ WITH [ ,...n ] ]

{ FOR | AFTER } LOGON

AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME   [ ; ] }

::=

    [ ENCRYPTION ]

    [ EXECUTE AS Clause ]

::=

    assembly_name.class_name.method_name

Corruption in triggers: Corruption in the MS SQL server database can happen due to several reasons like virus attack; meta-data structure corruption, power failure, hardware problem, and many more. A database administrator can restore corrupted triggers from the good known backup, if available.

How to restore without backup: If you don't have good known backup of the corrupted triggers then the best method for you to try any third party recovery software. I would like to suggest you Stellar Phoenix SQL recovery. It is advanced recovery software that recovers corrupt triggers of the SQL server database.