A block of code is called Triggers which constitutes a set of T-SQL statements which are activated in response to some certain actions. A special kind of stored procedure can also be interpreted as trigger which are executed whenever an action, such as modification of data, takes place. and Where Triggers are Used. A trigger is always defined on a table, and is said to be fired whenever the data in the underlying table is affected by any of the Data Manipulation.
Language (DML) statements : INSERT, UPDATE or DELETE. A trigger fires in response to events like insertion, updating, and deletion of data. Maintaining reliable, consistent and correct data in tables triggers are very helpful. These enable complex actions to be performed, and cascade these actions to the other dependent tables. The characteristics of a trigger are as follows: It can be associated only with tables. it It cannot be defined on temporary tables or views. However, a trigger can reference temporary tables and views. It It is fired automatically by SQL Server when any data modification statement is issued. It cannot be explicitly invoked or executed, as in the case of stored procedures. The nesting of triggers occurs when a trigger performs an action that initiates another trigger.
It prevents incorrect, unauthorized or inconsistent changes in data. It cannot return data to the user.
FOREIGN KEY constraints do not support cascading activities, and they can validate a column value only with an exact match to a value in another column. If your application requires that a delete from one table will also result in a delete of related data in another table, or if the referential integrity requires something other than this, you must use a trigger. li A CHECK constraint can validate a column value only against a logical expression or another column in the same table. If your application requires that a column value be validated against a column in another table, you must use a trigger.
TYPES OF TRIGGERS
SQL Server supports the following types of triggers: Insert Delete Update
Insert Trigger : An INSERT trigger is fired whenever an attempt is made to insert a row in the trigger table. When an INSERT statement is issued, a new row is added to both, the trigger table and the inserted table. The inserted table is a logical table that holds a copy of the rows that that have been inserted into the trigger table.
DELETE Trigger : A DELETE trigger is used whenever one will make an act to delete rows from the trigger table. When a DELETE statement is issued, the rows from the trigger table are deleted and added to the deleted table. The deleted table and the trigger table do not have any rows in common, while in the case of an insert trigger, the inserted table and the trigger table have the inserted rows in common. Example
CREATE TRIGGER trg DeletePub –
ON Publishers
FOR DELETE
AS
DECLARE @RowsDeleted int
DECLARE @RowsLeft int
DECLARE @RowsLeft__char char (10)
DECLARE @Message char(30)
SELECT @RowsDeleted = (SELECT COUNT() FROM deleted)
SELECT @RowsLeft = {SELECT COUNT() FROM Publishers)
IF @RowsDeleted > 5
BEGIN
SELECT @RowsLeftChar = CONVERT (varchar(10), @RowsLeft)
SELECT @Message = * Only % + @RowsLeftChar – are