Search questions and share your knowledge with the world here on Questionpoint.
Home > SQL Server Triggers Interview Questions > What is trigger in sql server

:: Categories ::

Interview Question in SQL Server Triggers


 

Interview Question :: What is trigger in sql server


what is trigger in sql server?
tell me an example.
Answers to "What is trigger in sql server"
RE: What is trigger in sql server?

A trigger is a special procedure, associated with a table, that is executed automatically when anything changes that table. You can designate a trigger to fire when one or more rows in the table are updated, deleted, inserted, or any/all of those. You can define more than one trigger for a given table.



While the trigger is executing, it has access to special in-memory pseudo-tables, called [inserted] and [deleted]. In an UPDATE trigger, the [deleted] table contains a copy of the row[s] as they were before the update, and the [inserted] table contains a copy of the same row[s] after the update. In a DELETE trigger, the [inserted] table is empty, and the [deleted] table contains a copy of the row[s] that were deleted. In an INSERT trigger... you get the idea.



For an example, suppose you wanted to log each time a new row is added, or an existing row is changed, for a table called [MainTable], including who made the change, and when:



CREATE TABLE [MainTable] (

id int IDENTITY(1,1) PRIMARY KEY NOT NULL,

field1 int NULL,

field2 int NULL

)

GO



CREATE TABLE [LogTable] (

id int IDENTITY(1,1) PRIMARY KEY NOT NULL,

MainTableID int NOT NULL,

ChangedBy varchar(128) NULL,

ChangeDate datetime DEFAULT (getdate()) NOT NULL

)

GO





CREATE TRIGGER tr_Log_MainTable_Changes

ON dbo.MainTable

FOR UPDATE, INSERT

AS

set nocount on



INSERT INTO [LogTable] (MainTableID, ChangedBy)

SELECT id, suser_sname() As ChangedBy

FROM [inserted]



GO





-- Now to test it, run the following statements:



insert into [MainTable] (Field1, Field2) values (2, 3)

insert into [MainTable] (Field1, Field2) values (4, 5)

update [MainTable] set Field1 = 7, Field2 = 8



select * from [LogTable] -- should return 4 rows
 
Vote for this answer ::  
RE: What is trigger in sql server?

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
 
Vote for this answer ::  
Update Alert Setting

Top Tags



- Sharepoint Developer Interview Questions - Java Interview Questions - J2EE Interview Questions - ASP.NET Interview Questions - MOSS 2007 Interview Questions - WPF Interview Questions - Microsoft CRM Interview Questions - Technical Interview Questions for Programmers
Home | Terms Of Service | Contact Us | Site Blog | Investors | Advertise With Us | Disclaimer | Privacy Policy | Questionpoint Widget
© Questionpoint 2008 User-posted content, unless source quoted, is licensed under a Creative Commons Public Domain License