what is the basic rules of trigger?  i don't understand how to make trigger on a particular table and why DECLARE command is used? 

• Changed type pituachModerator 17 hours 21 minutes ago this is a question

Good day kiron d challenger,

Those links can give you the explanation in simple way:

> https://technet.microsoft.com/en-us/library/ms187834(v=sql.105).aspx

> http://www.codeproject.com/Articles/25600/Triggers-SQL-Server

> http://www.sqlteam.com/article/an-introduction-to-triggers-part-i

* I did not read all the tutorial there, but they simed to be simple.
The last link is not 100% accurate, but it explain in very simple way. For example "A trigger is a database object that is attached to a table". (1) Actually a trigger can be server object (or you can call it system databases object but it is on the server instance level), like TRIGGER on LOGIN. (2) A trigger is not only attached to a table, but can be "attached" to other object like views. 

Usually you don't use DECLARE in a trigger. More precisely, it is not uncommon to see a trigger that goes:

  CREATE TRIGGER my_tri ON tbl FOR INSERT, UPDATE AS
  DECLARE @var1 int,
          @var2 varchar(20),
          @var3 datetime

  SELECT @var1 = col1, @var2 = col2, @var3 = col3
  FROM   inserted

But this is wrong. A trigger fires once per statement, and a statement can affect zero to many rows. You should never assume that the INSERT/UPDATE/DELETE only affects one row.

The basic rules of a trigger are as follows:

1) In a trigger you can refer to the virtual tables "inserted" and "deleted". The table "inserted" holds the row inserted by an INSERT statement and for an UPDATE statement the update rows as they look after the update. "deleted" holds the rows deleted by a DELETE statement and for an UPDATE, it is a before-image of the updated rows.

2) A trigger always executes in a transaction. If there is no user-defined transaction, the trigger executes in the context of the statement that fired the trigger.

3) An error in a trigger aborts the batch and rolls back the transaction, including the statement that fired the trigger. If you don't want that to happen, you sould not use a trigger.

Hi,

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

There are DML, DDL, or logon triggers.

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. For more information, see DML Triggers.

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.

Few Notable rules:

• The name of a trigger should follow the rules for identifiers.

• 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. Moreover, 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.

• AFTER triggers cannot be defined on views.

• AFTER is the default, if FOR is the only keyword specified.

• Edited by KEAARPEE 13 hours 15 minutes ago

what is the basic rules of trigger?  i don't understand how to make trigger on a particular table and why DECLARE command is used? 

As far as I've seen you use DECLARE commands only when you do some row by row processing inside triggers. In almost all the occasions I've come across this row by row logic can be replaced by a set based processing logic inside trigger which will perform much better compared to row by row approach.

You can use DELETED and INSERTED temporary tables for set bsed processing which will have details of all rows that got affected by the DML operation for which the trigger is defined.