what is trigger in sql server?

Question

what is trigger in sql server?
tell an example.

Answer 1

A trigger is a special kind of stored procedure that executes when an INSERT, UPDATE, or DELETE statement modifies the data in a specified table. A trigger can query other tables and can include complex Transact-SQL statements. You often create triggers to enforce referential integrity or consistency among logically related data in different tables.



Example - Implementing an INSERT Trigger:

CREATE TRIGGER [insrtWorkOrder] ON [Production].[WorkOrder]

AFTER INSERT AS

BEGIN

SET NOCOUNT ON;

INSERT INTO [Production]. [TransactionHistory] (

[ProductID], [ReferenceOrderID], [TransactionType], [TransactionDate], [Quantity],[ActualCost] )

SELECT inserted.[ProductID], inserted.[WorkOrderID], 'W', GETDATE(), inserted.[OrderQty], 0

FROM inserted;

END;



Example 2 - Implementing a DELETE Trigger:

CREATE TRIGGER [delCategory] ON [Categories]

AFTER DELETE AS

BEGIN

UPDATE P SET [Discontinued] = 1

FROM [Products] P INNER JOIN deleted as d

ON P.[CategoryID] = d.[CategoryID]

END;



Hope this helps

Answer 2

A Trigger is a procedure that activates when something else happens. There are two types of triggers in SQL Server, INSTEAD OF and AFTER triggers. They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions.



For example you might use a trigger to add records to another table if a record is added to one table.



Or a Trigger might delete all the records relating to a deleted key.



Or automatically add a datetime stamp to a record.

Answer 3

Joe C is in fact maximum suitable different than related to multi-row updates - a series off could properly be special to fire the two as quickly as in spite of the style of rows affected or for each row affected. If for each row, then inserted/deleted will relate to purely the specific row in question, that's what you prefer.